Indexing in MongoDB: The ESR Rule and Explain Plans

Without an index, MongoDB scans every document in a collection (COLLSCAN). Indexes make queries fast, and compound index order matters as much as in SQL.

Creating indexes

await orders.createIndex({ customerId: 1 });
await orders.createIndex({ status: 1, createdAt: -1 });
await users.createIndex({ email: 1 }, { unique: true });
await sessions.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 });  // TTL: auto-delete
await orders.createIndex({ 'items.sku': 1 });   // multikey: indexes each array element

The ESR rule for compound indexes

Order fields as Equality, then Sort, then Range:

// Query: paid orders for a customer, above 500, newest first
orders.find({ customerId: 7, status: 'paid', total: { $gt: 500 } }).sort({ createdAt: -1 });

// Good index: equality fields, then the sort field, then the range field
await orders.createIndex({ customerId: 1, status: 1, createdAt: -1, total: 1 });

Putting the range field before the sort field forces an in-memory sort, which is slow and limited in size.

Check with explain()

const plan = await orders
  .find({ customerId: 7, status: 'paid' })
  .sort({ createdAt: -1 })
  .explain('executionStats');

console.log(plan.queryPlanner.winningPlan);           // look for IXSCAN, not COLLSCAN
console.log(plan.executionStats.totalDocsExamined);    // ideally close to nReturned
console.log(plan.executionStats.nReturned);

• IXSCAN - index used. COLLSCAN - full scan.
• SORT stage - in-memory sort; the index doesn't match the sort.
• totalDocsExamined far above nReturned - the index isn't selective enough.

Covered queries

orders.find({ customerId: 7 }, { projection: { _id: 0, status: 1, createdAt: 1 } });
// with an index on { customerId: 1, status: 1, createdAt: 1 }, MongoDB never reads the documents

Keep it lean

Each index slows writes and uses RAM. Use $indexStats to find unused indexes, and remember a compound index on { a, b } already serves queries on a alone.