MongoDB Index Tuning and the ESR Rule

Most MongoDB performance problems trace back to a single root cause: indexes that don't match how queries actually execute. The ESR rule—Equality, Sort, Range—is the most practical mental model for designing compound indexes that work with MongoDB's B-tree structure rather than against it. Get this right, and your queries run in milliseconds. Get it wrong, and you're scanning millions of documents for every request.

Why Index Order Matters in MongoDB

MongoDB's compound indexes are ordered lists of field references. When a query uses an index, MongoDB walks the index tree in the exact order the fields appear in the index definition. If your query filters on status and createdAt, but your index starts with createdAt, MongoDB can only use the first field efficiently—the rest becomes a scan within the index. This ordering determines whether your query is a precise lookup or a slow filter.

The B-tree structure means that only the leftmost prefix of the index is used for equality matches. Sorting and range conditions require the fields to appear after the equality fields in the index. Violate this ordering, and you force MongoDB to sort in memory or scan unnecessary index entries.

The ESR Rule: Equality, Sort, Range Explained

The ESR rule defines the optimal field order in a compound index:

• Equality fields first: Fields that use exact matches (=, $in) should appear first. These narrow the search space most efficiently.
• Sort fields second: Fields used in .sort() come next. MongoDB can walk the index in sort order without additional sorting.
• Range fields last: Fields with range conditions ($gt, $lt, $ne, $regex) go last. Range scans stop the index from being used for subsequent fields.

Consider a query filtering by status = 'active', sorting by createdAt descending, and filtering by amount > 100. The ESR-optimized index would be:

db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 })

Equality on status first, sort on createdAt second, range on amount last. This allows MongoDB to locate the exact status block, walk createdAt in reverse order, and apply the amount filter as it scans.

Applying ESR to Real-World Query Patterns

Start by analyzing your application's most frequent query patterns. For each query, identify which fields are equality, sort, and range conditions. Build indexes that serve multiple queries when possible, but prioritize the most expensive ones.

Example: An e-commerce orders collection with queries like:

db.orders.find({
  status: "shipped",
  createdAt: { $gte: ISODate("2024-01-01") }
}).sort({ total: -1 })

Here status is equality, total is sort, and createdAt is range. The ESR index would be:

db.orders.createIndex({ status: 1, total: -1, createdAt: 1 })

Note that the sort field (total) comes before the range field (createdAt). If you reversed them, MongoDB would need to sort the results in memory.

Common ESR Violations and Their Impact

The most frequent mistake is putting range fields before sort fields. Example: { createdAt: 1, status: 1 } for a query that sorts by createdAt and filters on status. This forces MongoDB to scan all index entries matching the createdAt range, then filter by status—wasting index efficiency.

Another violation is placing sort fields after range fields. If your index is { status: 1, amount: 1, createdAt: -1 } and the query ranges on amount and sorts on createdAt, MongoDB cannot use the index for sorting because the range field interrupts the sort order. The result is an in-memory sort, which fails on large result sets.

Rule of thumb: In a compound index, never put a range field before a sort field. Equality first, sort second, range last—always.

Using explain() to Validate Index Effectiveness

Always validate index usage with explain("executionStats"). Key metrics to check:

• stage: Should be IXSCAN (index scan), not COLLSCAN (collection scan).
• totalDocsExamined: Should match nReturned closely. Large disparity means inefficient filtering.
• nReturned: The number of documents returned.
• executionTimeMillis: Should be low relative to the data size.
• sortStage: If present, your index isn't covering the sort order.

Example explain output check:

db.orders.find({ status: "shipped" }).sort({ createdAt: -1 }).explain("executionStats")
// Look for: "stage" : "IXSCAN", no "SORT" stage, totalDocsExamined == nReturned

If you see a SORT stage, your index order is wrong or missing the sort field.

Advanced Considerations: Compound Indexes and Covered Queries

When all fields in a query (filter, sort, projection) are contained in the index, MongoDB can satisfy the query entirely from the index without touching documents. This is a covered query. To achieve this, include all projected fields in the index, following ESR order.

For example, if queries always return status, createdAt, and total, and the query filters on status and sorts on createdAt, an index of { status: 1, createdAt: -1, total: 1 } can cover the query. Check with explain() for "stage" : "PROJECTION_COVERED".

Be careful with $in operators: they count as equality for index ordering but can produce multiple index scans. For $in on high-cardinality fields, consider if a different index order might reduce scan count.

Key Takeaways for Index Design in MongoDB

• Always apply ESR: Equality fields first, sort fields second, range fields last.
• One index can serve multiple queries if the leftmost prefix matches common equality patterns.
• Validate every index with explain("executionStats")—never guess.
• Avoid range fields before sort fields; this guarantees an in-memory sort.
• Covered queries eliminate document reads—aim for them in read-heavy workloads.
• Monitor index usage with $indexStats and drop unused indexes.

The short version

Design compound indexes with equality fields first, then sort fields, then range fields. Validate with explain(). Never put range before sort. If you follow ESR, MongoDB's B-tree works for you, not against you.