What is a compound index and how does the index prefix rule work?
Learn how MongoDB compound indexes work, why field order matters, and how the left-prefix and ESR rules decide which queries and sorts your index can serve.
Expected Interview Answer
A compound index in MongoDB is a single index built on two or more fields, storing entries sorted first by the leading field, then by the next, and so on. The index prefix rule says a query can use the index only if it filters on a contiguous left-to-right prefix of those fields, starting with the first.
Because the B-tree keys are ordered by the field sequence, MongoDB can efficiently seek on the leftmost field and any following fields in order. A query on { a, b } can use an index on { a: 1, b: 1, c: 1 } (a prefix), and so can a query on { a }, but a query on only { b } or { c } cannot use it as an index scan because the leading field is missing. Field order also determines which sort orders the index can satisfy without an in-memory sort.
- One index serves multiple query shapes via its prefixes
- Supports equality-then-range-then-sort access patterns efficiently
- Avoids in-memory sorts when sort keys match the index order
- Reduces the total number of indexes to maintain
- Enables covered queries when all projected fields are in the index
AI Mentor Explanation
A batting order is a compound index: openers first, then middle order, then tail. You can pick a contiguous run starting from the top — openers, or openers plus number three — and know exactly where they slot in. But you cannot jump straight to 'give me only the number-five batter' without first accounting for everyone above them; the sequence only lets you seek forward from the opener onward.
Step-by-Step Explanation
Step 1
Choose field order deliberately
Put equality-match fields first, then a range field, then sort fields (the ESR rule) so the prefix serves your hot queries.
Step 2
Create the index
Run db.collection.createIndex({ a: 1, b: 1, c: 1 }) — MongoDB builds one B-tree ordered by a, then b, then c.
Step 3
Match a left-anchored prefix
Queries filtering on { a }, { a, b }, or { a, b, c } can use it; a query on only { b } cannot.
Step 4
Align sorts with the index order
A sort on { a: 1, b: 1 } is served by the index; a sort that skips the leading field triggers an in-memory sort.
Step 5
Verify with explain
Use explain('executionStats') and confirm an IXSCAN (not COLLSCAN) and no in-memory SORT stage.
What Interviewer Expects
- Understanding that field order is significant, not interchangeable
- Ability to state the left-prefix rule precisely
- Knowledge of the ESR (equality, sort, range) ordering guideline
- Awareness that indexes also serve sorts
- Using explain to prove index usage
Common Mistakes
- Believing a compound index works regardless of queried field order
- Thinking { a: 1, b: 1 } is the same as { b: 1, a: 1 }
- Assuming a query on only the second field uses the index
- Ignoring how field order affects whether a sort is covered
- Creating many single-field indexes instead of one well-ordered compound index
Best Answer (HR Friendly)
“A compound index is one index built over several fields kept in a fixed order, like a phone book sorted by last name then first name. It speeds up searches only when you look things up starting from the leading field and moving rightward, so choosing the field order to match your common queries is the key skill.”
Code Example
// Build one index ordered: status, then userId, then createdAt
db.orders.createIndex({ status: 1, userId: 1, createdAt: -1 })
// Uses the index (leading-prefix { status })
db.orders.find({ status: 'shipped' })
// Uses the index ({ status, userId } prefix) and the sort is covered
db.orders.find({ status: 'shipped', userId: 42 }).sort({ createdAt: -1 })
// Does NOT use it as an index scan — userId is not a leading prefix
db.orders.find({ userId: 42 })
// Confirm the plan uses IXSCAN, not COLLSCAN
db.orders.find({ status: 'shipped', userId: 42 }).explain('executionStats')Follow-up Questions
- What is the ESR (Equality, Sort, Range) rule and why does it matter?
- How does index field order affect sort performance?
- When would you prefer several single-field indexes over one compound index?
- Can a compound index support a covered query? What conditions are needed?
- How do you detect an unused or redundant index in production?
MCQ Practice
1. Given an index { a: 1, b: 1, c: 1 }, which query can use it as an index scan?
{ a, b } is a contiguous left-anchored prefix, so the index applies. Queries missing the leading field 'a' cannot use it as an index scan.
2. What does the ESR rule recommend for compound index field order?
ESR = Equality, Sort, Range: put exact-match fields first, then sort keys, then range predicates for the most selective, sort-friendly index.
3. Why can index field order affect whether a sort avoids an in-memory sort?
The B-tree is physically ordered by the field sequence, so a sort matching that order (or its reverse) is satisfied by reading the index, avoiding a blocking SORT stage.
Flash Cards
What is a compound index? — A single index on two or more fields, with entries sorted by the fields in the order declared.
State the index prefix rule. — A query can use a compound index only if it filters on a contiguous left-to-right prefix that starts with the first field.
Is { a: 1, b: 1 } the same as { b: 1, a: 1 }? — No. Field order changes which query prefixes and sorts the index can serve.
What is the ESR rule? — Order compound index fields as Equality, then Sort, then Range for best selectivity and sort coverage.
How do you prove an index is used? — Run explain('executionStats') and look for an IXSCAN stage with no in-memory SORT.