What Is a Compound Index in MongoDB?
Learn what a compound index is in MongoDB, how field order and the ESR rule affect query performance, and how to verify index usage with explain.
Expected Interview Answer
A compound index is a single MongoDB index built on two or more fields together, allowing queries and sorts that use those fields in the same order (a prefix of them) to be served efficiently from one index instead of scanning documents or combining separate single-field indexes.
You create a compound index by passing an object with multiple fields and their sort direction, such as { userId: 1, createdAt: -1 }. MongoDB stores the index entries sorted first by userId, then by createdAt within each userId, which is why field order matters enormously: a query or sort that matches a left-to-right prefix of the indexed fields (just userId, or userId plus createdAt) can use the index efficiently, but a query that only filters on createdAt without userId generally cannot use this compound index efficiently. This is known as the equality-sort-range rule of thumb for ordering fields: put equality filters first, then fields used for sorting, then range filters last. Following this rule minimizes the number of index entries MongoDB has to scan.
- Serves multi-field queries and sorts from a single index scan
- Avoids expensive in-memory sorts when the index order matches the query's sort
- Reduces the number of separate indexes needed, saving write overhead and storage
- Can support covered queries when all needed fields are in the index
- Field order can be tuned with the equality-sort-range rule for optimal performance
AI Mentor Explanation
A compound index is like a scorebook organized first by innings, then by over within each innings, then by ball. Wanting every ball of the third over in the second innings, you flip straight to that section instead of scanning the book, but wanting every third ball across all overs regardless of innings, the layout wouldn't help you jump directly there.
Step-by-Step Explanation
Step 1
Define the field order
Create the index with createIndex({ field1: 1, field2: -1, ... }), choosing field order deliberately since it determines which queries can use it.
Step 2
Apply the ESR rule
Order fields as Equality filters first, then Sort fields, then Range filters, to minimize the index entries scanned.
Step 3
Match a left-to-right prefix
Queries and sorts must use a prefix of the indexed fields in order to benefit; skipping a field breaks the prefix match.
Step 4
Check with explain()
Run explain('executionStats') to confirm the query uses IXSCAN on the compound index rather than a COLLSCAN or an inefficient index scan.
Step 5
Consider covered queries
If the query only touches fields present in the compound index (including as the projection), MongoDB can answer entirely from the index without reading documents.
What Interviewer Expects
- Explains that a compound index covers multiple fields in a defined order
- Understands the left-to-right prefix rule for index usability
- Knows the equality-sort-range (ESR) guideline for field ordering
- Can describe how sort direction in the index affects supported sorts
- Mentions using explain() to verify index usage
Common Mistakes
- Assuming a compound index on {a, b} speeds up queries filtering only on b
- Ignoring field order and putting range filters before equality filters
- Creating redundant single-field indexes that a compound index already covers
- Forgetting that sort direction combinations matter for using the index to satisfy a sort
Best Answer (HR Friendly)
“A compound index is a way to make database lookups fast when you're filtering or sorting by more than one piece of information at once, like searching by user and then by date. Ordering the fields correctly in the index is important, because it determines which kinds of searches it can speed up.”
Code Example
// Equality on userId, sort by createdAt descending
db.orders.createIndex({ userId: 1, createdAt: -1 });
// Uses the index efficiently: equality prefix + matching sort
db.orders.find({ userId: 'u123' }).sort({ createdAt: -1 });
// Verify with explain
db.orders.find({ userId: 'u123' })
.sort({ createdAt: -1 })
.explain('executionStats').executionStats.executionStages.stage;
// 'IXSCAN' confirms the compound index was usedFollow-up Questions
- What is the equality-sort-range (ESR) rule for ordering compound index fields?
- Can a query use only a prefix of a compound index, and what does that mean?
- How does a compound index differ from creating two separate single-field indexes?
- What is a covered query and how does a compound index enable one?
- How would you decide index field order for a query with both filtering and sorting?
MCQ Practice
1. For a compound index { a: 1, b: 1, c: 1 }, which query can use it efficiently?
A query must match a left-to-right prefix of the indexed fields, so filtering on a and b (in order) can use the index; skipping a cannot.
2. What does the ESR rule recommend for ordering compound index fields?
ESR stands for Equality, Sort, Range — put equality filters first, then sort fields, then range filters, to minimize scanned entries.
3. What is a covered query in the context of a compound index?
A covered query is one where all requested fields exist in the index itself, so MongoDB never needs to fetch the actual documents.
Flash Cards
What is a compound index? — A single index built on two or more fields together, ordered to serve multi-field queries and sorts efficiently.
What is the ESR rule? — Order compound index fields as Equality filters, then Sort fields, then Range filters, to minimize scanned entries.
What is the prefix rule for compound indexes? — A query or sort can use the index only if it matches a left-to-right prefix of the indexed fields.
How do you verify a query is using a compound index? — Run explain('executionStats') and check that the stage is IXSCAN, not COLLSCAN.