Compound Index Prefix Rule and ESR Principle
Learners will apply the Equality-Sort-Range index design principle to compound indexes for maximum query coverage.
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' })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