The ESR rule (Equality, Sort, Range) is a MongoDB indexing principle that guides how to order fields in a compound index for optimal query performance. It states that fields should be arranged in this sequence: first, fields on which you perform equality checks; second, the field used for sorting; and finally, fields used for range queries (like $gt, $lt, $in). Following this order allows MongoDB to use the index most efficiently, often resulting in covered queries and eliminating in-memory sorts.
The order of fields in a compound index is critical because MongoDB uses the index in a prefix-based manner. The index is structured as a tree where the first field determines the top-level order, the second field determines order within matching first-field values, and so on. If the query doesn't match the index order, MongoDB may still use the index but less efficiently—it might need to scan more index entries or perform in-memory sorting. The ESR rule ensures the index structure aligns with how the query will be executed.
Equality first: Fields with exact match conditions (like
status: "active") should come first because they narrow down the result set most effectively. Once MongoDB finds the exact value in the index, it can focus on the subtree containing only matching documents.Sort second: The sort field comes next so MongoDB can traverse the index in order, avoiding an in-memory sort operation. When the sort field is part of the index in the correct order, results come back already sorted.
Range last: Range conditions (like
age: { $gt: 25 }) come last because they typically match multiple values. Placing them after equality and sort fields allows MongoDB to scan a contiguous portion of the index.
Compound indexes work by prefix—the first field provides the primary ordering, the second field provides secondary ordering within each first-field value, and so on. This means an index on { a:1, b:1, c:1 } can efficiently support queries on a, a+b, and a+b+c. It can partially support queries on a+c (using a only, then filtering c in memory). But it cannot support queries that skip a entirely. The ESR rule ensures your most important query patterns align with the index prefix structure.
The ESR rule assumes a single compound index for a query. Some scenarios require different strategies: queries with multiple range conditions may need to choose the most selective range first; queries sorting on multiple fields require careful ordering; and some queries might be better served by multiple indexes combined with index intersection. Additionally, if you have multiple important query patterns, you may need multiple indexes, each optimized with ESR for its specific pattern.
Placing fields in the wrong order can force MongoDB to scan many more index entries or perform in-memory sorts. For example, with index { age:1, status:1, name:1 } on our earlier query, MongoDB would: scan all index entries for age > 25 (potentially millions), filter to "active" status in memory, then sort by name in memory. This could be thousands of times slower than using the ESR-ordered index. The difference grows with data size—on a 10-million document collection, wrong order might mean scanning millions of index entries versus scanning only the matching few thousand.
Always apply ESR when designing compound indexes for specific queries
Use explain() to verify index usage and check for in-memory sort stages
Consider all important query patterns—you may need multiple indexes
Sort direction matters: index direction should match sort direction for the sort field
The ESR rule is a guideline, not an absolute law—test with your actual data