Explain the ESR (Equality, Sort, Range) rule. Why is the order of fields in a compound index so critical?

Question

Accepted Answer

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. ```javascript
// Consider this query:
db.users.find(
  { status: "active", age: { $gt: 25 } }  // Equality on status, range on age
).sort({ name: 1 })  // Sort by name

// Following ESR rule:
// Equality field: status
// Sort field: name
// Range field: age

db.users.createIndex({ status: 1, name: 1, age: 1 })

// Why this order works:
// 1. MongoDB finds all "active" documents using the first field
// 2. Within those, the index is already sorted by name, so no in-memory sort needed
// 3. Finally, it scans only the age range within the sorted results

// ❌ Wrong order (would need in-memory sort):
db.users.createIndex({ status: 1, age: 1, name: 1 })
``` **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. ```javascript
// Scenario: E-commerce product search
db.products.find(
  { 
    category: "electronics",     // Equality
    price: { $lt: 1000 }         // Range
  }
).sort({ popularity: -1 })       // Sort

// ESR-compliant index
db.products.createIndex({ 
  category: 1,    // Equality first
  popularity: -1, // Sort second (matches sort direction)
  price: 1        // Range last
})

// MongoDB can:
// 1. Jump to "electronics" category in the index
// 2. Within that category, traverse in popularity order
// 3. Stop when price exceeds $1000
// Result: Efficient, no in-memory sort

// Check index usage with explain()
db.products.find(
  { category: "electronics", price: { $lt: 1000 } }
).sort({ popularity: -1 }).explain("executionStats")
// Look for: totalDocsExamined should equal nReturned, no SORT stage
``` 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—y

Explain the ESR (Equality, Sort, Range) rule. Why is the order of fields in a compound index so critical?

Explain the ESR (Equality, Sort, Range) rule. Why is the order of fields in a compound index so critical?

Scenario Questions

Follow-up Questions