What is the ESR rule?

In compound indexes, put fields used for Equality first, then fields used for Sort, then fields used for Range conditions.

How do you tell from explain output that a query is inefficient?

A COLLSCAN stage, or totalDocsExamined much larger than nReturned.

What is a covered query?

A query whose filter and projected fields are all in one index (with _id excluded unless indexed), so MongoDB never reads the documents.

Indexes and explain() — MongoDB from Basics to Production

What you'll build

A collection of 5,000 orders queried by customer and date: the query plan without an index (a collection scan), with a compound index in the right order, and as a covered query that never touches the documents.

The idea

Without an index, MongoDB scans every document (COLLSCAN). An index (IXSCAN) jumps to matching entries.
Compound indexes follow the ESR rule: Equality fields first, then Sort fields, then Range fields.
explain("executionStats") shows the winning plan, totalDocsExamined and nReturned; examined should be close to returned.
A covered query only uses indexed fields (projection excludes _id unless indexed), so no documents are read.

The code

From COLLSCAN to a covered index query

const docs = [];
for (let i = 0; i < 5000; i++) {
  docs.push({ customerId: "c" + (i % 100), createdAt: new Date(Date.UTC(2026, 0, 1 + (i % 60))), total: (i % 50) + 5 });
}
db.orders.insertMany(docs);

const query = { customerId: "c42", createdAt: { $gte: new Date("2026-02-01") } };
const stats = (cursor) => {
  const e = cursor.explain("executionStats");
  const plan = JSON.stringify(e.queryPlanner.winningPlan).match(/"stage":"(COLLSCAN|IXSCAN)"/)[1];
  const s = e.executionStats;
  return `${plan}, examined ${s.totalDocsExamined} docs / ${s.totalKeysExamined} keys, returned ${s.nReturned}`;
};
print("no index:     ", stats(db.orders.find(query).sort({ createdAt: -1 })));

db.orders.createIndex({ customerId: 1, createdAt: -1, total: 1 });     // equality, sort/range, then covered field
print("ESR index:    ", stats(db.orders.find(query).sort({ createdAt: -1 })));
print("covered query:", stats(db.orders.find(query, { _id: 0, createdAt: 1, total: 1 }).sort({ createdAt: -1 })));
print("indexes:", db.orders.getIndexes().map(i => i.name).join(", "));

const docs = [];
for (let i = 0; i < 5000; i++) {
  docs.push({ customerId: "c" + (i % 100), createdAt: new Date(Date.UTC(2026, 0, 1 + (i % 60))), total: (i % 50) + 5 });
}
db.orders.insertMany(docs);

db.orders.createIndex({ customerId: 1, createdAt: -1, total: 1 });     // equality, sort/range, then covered field
print("ESR index:    ", stats(db.orders.find(query).sort({ createdAt: -1 })));
print("covered query:", stats(db.orders.find(query, { _id: 0, createdAt: 1, total: 1 }).sort({ createdAt: -1 })));
print("indexes:", db.orders.getIndexes().map(i => i.name).join(", "));

Output

no index:      COLLSCAN, examined 5000 docs / 0 keys, returned 17
ESR index:     IXSCAN, examined 17 docs / 17 keys, returned 17
covered query: IXSCAN, examined 0 docs / 17 keys, returned 17
indexes: _id_, customerId_1_createdAt_-1_total_1

Run this on your computer — see “Get Started” for setup.

Common mistakes

Indexing every field "just in case": indexes cost memory and slow writes.
Compound indexes in the wrong order (range field first), forcing in-memory sorts.
Never checking explain and finding the collection scan in production.
Creating indexes on large production collections without considering the build load; schedule and monitor them.

Key points

Check plans with explain("executionStats"): examined vs returned.
Order compound indexes by equality, sort, range.
Covered queries read only the index.

Further reading: Indexes · The ESR rule · Explain results

What you'll build

The idea

The code

Common mistakes

Key points

Practice questions