Compound Index Prefix Rule and ESR Principle
Learners will apply the Equality-Sort-Range index design principle to compound indexes for maximum query coverage.
Compound Index Prefix Rule and ESR Principle is a free MongoDB Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the MongoDB Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Compound Index Field Order Matters
A compound index on multiple fields can serve a wide range of queries — but only if the fields appear in the right order. MongoDB can use a compound index to satisfy a query only if the query's filter matches a prefix of the index. The ordering of fields in the index definition directly controls which queries benefit from it.
The Prefix Rule Explained
A compound index { a: 1, b: 1, c: 1 } can be used by queries that filter on: { a }, { a, b }, or { a, b, c }. These are the prefixes. A query filtering only on { b } or { b, c } cannot use this index — it would do a collection scan. The index is like a phone book sorted by last name, then first name: you can look up by last name alone or by last + first, but not by first name alone.
// Index: { status: 1, customerId: 1, createdAt: 1 }
db.orders.createIndex({ status: 1, customerId: 1, createdAt: 1 })
// Uses index (prefix: status)
db.orders.find({ status: 'pending' })
// Uses index (prefix: status + customerId)
db.orders.find({ status: 'pending', customerId: 'c001' })
// Does NOT use index (no leading prefix)
db.orders.find({ customerId: 'c001' })The ESR Principle: Equality, Sort, Range
The ESR principle is a field-ordering rule for compound indexes: put Equality fields first, Sort fields second, and Range fields last. This ordering maximises the portion of the query the index can satisfy and minimises the number of index entries that must be examined. ESR is the most important compound index design rule in MongoDB.
// Query: find pending orders for customer c001, sorted by date,
// for dates after Jan 2025
// E: status = 'pending' (equality)
// S: createdAt (sort)
// R: customerId in ['c001','c002'] (range / $in)
// ESR-ordered index
db.orders.createIndex({ status: 1, createdAt: 1, customerId: 1 })Why Equality Fields Come First
Equality predicates (field: value or $eq) narrow the index scan to a single, fixed value. Placing them first dramatically reduces the number of index entries the query planner needs to consider. Once equality has pinpointed the exact bucket of matching keys, the sort and range operations work on a much smaller dataset.
// E first: status equality narrows to ~5% of index
// Then sort on createdAt within that slice
// Then range on amount within that sorted slice
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })
db.orders.find({ status: 'shipped' })
.sort({ createdAt: -1 })
.hint({ status: 1, createdAt: 1, amount: 1 })Why Sort Fields Come Before Range
Placing sort fields before range fields allows MongoDB to use the index to satisfy the sort without a blocking in-memory sort. If range fields come before sort fields, MongoDB must scan all matching range documents, sort them in memory, then return results — adding CPU and memory overhead. With sort fields second, results emerge from the index already in the correct order.
// Without ESR: range before sort forces in-memory sort
db.orders.createIndex({ status: 1, amount: 1, createdAt: 1 })
db.orders.find({ status: 'pending', amount: { $gt: 50 } })
.sort({ createdAt: 1 })
// explain() shows: SORT stage (in-memory sort needed)
// With ESR: sort before range avoids in-memory sort
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })
// explain() shows: no SORT stageRange Fields Last: Why It Works
Range predicates like $gt, $lt, $gte, $lte, $in, and regex span a contiguous portion of the index. By placing them last, MongoDB first narrows results with equality and delivers them in sort order, then applies the range check as a final filter. The index scan stays efficient because range does not break the sorted traversal order set by the sort fields.
// ESR applied correctly
// E: userId (equality)
// S: timestamp (sort)
// R: score (range)
db.events.createIndex({ userId: 1, timestamp: 1, score: 1 })
db.events.find({
userId: 'u123',
score: { $gte: 80 }
}).sort({ timestamp: -1 })Handling $in: Range or Equality?
$in with a small list of values behaves more like equality and can be placed first. When the list is large, it acts more like a range and should go later in the index. A useful rule: if the $in list has fewer than 10–20 values and you query it frequently, treat it as equality (first). For large, dynamic lists, treat it as range (last).
// Small $in (2 values) — treat as equality, put first
db.orders.createIndex({ status: 1, createdAt: 1 })
db.orders.find({ status: { $in: ['pending', 'processing'] } })
.sort({ createdAt: -1 })
// Large $in — treat as range, put last
db.orders.createIndex({ region: 1, createdAt: 1, userId: 1 })
db.orders.find({
region: 'EU',
userId: { $in: hundredsOfUserIds }
}).sort({ createdAt: -1 })Verifying ESR With explain()
Always verify your index design with explain('executionStats'). Look for: IXSCAN (index scan) — good. COLLSCAN (collection scan) — missing index. SORT stage present — in-memory sort, index field order might be wrong. keysExamined / nReturned should be close to 1 for an optimal compound index.
db.orders.find({ status: 'pending', amount: { $gt: 50 } })
.sort({ createdAt: 1 })
.explain('executionStats')
// Good: { stage: 'IXSCAN', nReturned: 42, keysExamined: 44 }
// Bad: { stage: 'COLLSCAN', nReturned: 42, docsExamined: 500000 }The Prefix Rule and Partial Index Reuse
Thanks to the prefix rule, a single well-designed compound index can replace several single-field indexes. An index on { a: 1, b: 1, c: 1 } makes separate indexes on { a: 1 } and { a: 1, b: 1 } redundant. Fewer indexes means less write overhead and less memory pressure — important for write-heavy workloads where index maintenance adds latency to every insert, update, and delete.
// One compound index replaces three single-field indexes
db.users.createIndex({ country: 1, city: 1, age: 1 })
// Redundant (covered by compound prefix rule):
// db.users.createIndex({ country: 1 }) -- REDUNDANT
// db.users.createIndex({ country: 1, city: 1 }) -- REDUNDANTIndex Selectivity and Field Order
Beyond ESR, consider selectivity — how many documents share the same value. Put the most selective equality field first (fewest duplicates). For example, userId is more selective than status. Placing the more selective field first narrows the scan faster. When multiple equality fields exist, order them most-selective to least-selective for maximum performance.
// userId is highly selective (millions of users)
// status is low-selectivity (only 5 values)
// More efficient: selective equality field first
db.orders.createIndex({ userId: 1, status: 1, createdAt: 1 })
// Less efficient: low-selectivity field first
db.orders.createIndex({ status: 1, userId: 1, createdAt: 1 })Putting ESR Into Practice
When designing a compound index, start by listing your top query's filter conditions and sort, then classify each field as E (equality), S (sort), or R (range). Build the index in that order. Run explain('executionStats') to confirm you see an IXSCAN with no SORT stage and a keysExamined/nReturned ratio near 1. Revisit the index whenever query patterns change.
// Practical checklist:
// Query: find users in 'NY' (E), sorted by signup (S), age > 18 (R)
// E: state = 'NY'
// S: signupDate
// R: age > 18
db.users.createIndex({ state: 1, signupDate: 1, age: 1 })
// Verify no in-memory sort and good key ratio:
db.users.find({ state: 'NY', age: { $gt: 18 } })
.sort({ signupDate: -1 })
.explain('executionStats')Quick Check
Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.
Lesson Recap
In this lesson you learned: a compound index can only be used when the query matches a prefix of the index fields, the ESR principle dictates ordering fields as Equality, Sort, Range for maximum query coverage, and placing sort fields before range fields eliminates costly in-memory sort stages. Next up we compare index intersection versus compound indexes.
Frequently asked questions
Is the “Compound Index Prefix Rule and ESR Principle” lesson free?
Yes — the full text of “Compound Index Prefix Rule and ESR Principle” is free to read here on the web, and the MongoDB Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the MongoDB Academy course, upgrade to CoddyKit PRO.
What will I learn in “Compound Index Prefix Rule and ESR Principle”?
Learners will apply the Equality-Sort-Range index design principle to compound indexes for maximum query coverage. You practise MongoDB Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start MongoDB Academy?
No prior experience is required. MongoDB Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Compound Index Prefix Rule and ESR Principle” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this MongoDB Academy lesson?
Yes. Every MongoDB Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- The Database Profiler and Slow Query Log
- Compound Index Prefix Rule and ESR Principle
- Index Intersection vs Compound Indexes
- Aggregation Pipeline Optimization Tips