What a Compound Index Is
A compound index indexes multiple fields together in a single B-tree, created with db.collection.createIndex({ a: 1, b: 1, c: -1 }). The order of fields matters enormously: MongoDB builds the index as a sorted structure on field a first, then within each value of a, sorted by b, then within each value of b, sorted by c — much like a phone book sorted by last name then first name. A query can efficiently use a compound index only if it constrains fields as a left-to-right prefix of that key order; a query filtering only on b or c without a would not be able to use the index for an equality seek.
Cricket analogy: A compound index on { team: 1, matchDate: -1 } is like an almanac sorted first by team and then by date within each team, so finding India's most recent match is fast, but finding all matches on a specific date across every team is not, since date isn't the leading key.
The ESR Rule: Equality, Sort, Range
MongoDB's official guidance for ordering compound index fields is the ESR rule: put Equality-match fields first, then Sort fields, then Range-query fields last. Equality fields go first because they let the index narrow to a single contiguous block immediately. Sort fields go next so MongoDB can read results already in the requested order straight off the index without an in-memory SORT stage. Range fields go last because a range condition (like $gt, $lt, $in with many values) breaks the ability to use subsequent index fields for further narrowing within that range.
Cricket analogy: For a query filtering status: 'completed' (equality), sorting by matchDate, and ranging over attendance > 20000, ESR says index { status: 1, matchDate: -1, attendance: 1 } — like first pulling only completed matches, already date-sorted, then scanning attendance within that narrow set.
// Compound index following the ESR rule
// Equality: status, Sort: createdAt, Range: totalAmount
db.orders.createIndex({ status: 1, createdAt: -1, totalAmount: 1 });
// This query can fully use the index (equality -> sort -> range)
db.orders.find({
status: "completed",
totalAmount: { $gt: 50 }
}).sort({ createdAt: -1 });
// A query on totalAmount alone CANNOT use this index efficiently
// because it skips the leading "status" prefix field.
db.orders.find({ totalAmount: { $gt: 50 } });A compound index on { a: 1, b: 1 } automatically supports queries on { a: 1 } alone (a valid prefix), but never supports efficient equality lookups on { b: 1 } alone — this is why field order is the single most important design decision for a compound index.
Covered Queries
A covered query is one where every field requested in the query filter and projection exists within the index itself, so MongoDB can answer the query by reading only the index and never touching the actual documents on disk. This is verified by an explain() plan showing totalDocsExamined: 0. Covered queries are significantly faster because the B-tree index pages are smaller and more likely to already be cached in RAM than the full documents, reducing I/O.
Cricket analogy: A covered query on { player: 1, team: 1 } for a query asking only for player and team is like Cricinfo answering 'who plays for which team' straight from a compact index card, never opening the full player biography file.
Adding _id or other unindexed fields to a projection breaks index coverage unless _id: 0 is explicitly excluded, since _id is included by default and typically is not part of the compound index being used.
- A compound index combines multiple fields into one sorted B-tree; field order determines which query shapes it can serve.
- The ESR rule orders fields as Equality, then Sort, then Range for maximum efficiency.
- A compound index on { a: 1, b: 1 } supports queries on the prefix { a: 1 } but not { b: 1 } alone.
- A covered query answers entirely from the index with totalDocsExamined: 0, avoiding document fetches.
- Exclude _id explicitly with _id: 0 in the projection to preserve index coverage.
- Sort order (1 vs -1) in a compound index matters when combined with multi-field sorts in opposite directions.
- Field order is the single most important design decision when creating a compound index.
Practice what you learned
1. In the ESR rule, what does the 'S' stand for?
2. Given an index on { a: 1, b: 1 }, which query can use it efficiently for an equality seek?
3. What indicates a query is 'covered' in an explain() plan?
4. Why should equality-match fields come first in a compound index per the ESR rule?
5. What must you typically do to keep a query covered when a projection excludes most fields?
Was this page helpful?
You May Also Like
Indexes in MongoDB
Learn how MongoDB indexes work internally, why they speed up queries, and how to create and manage the core index types.
The Query Planner and explain()
Learn how MongoDB's query planner selects an execution plan and how to use explain() to diagnose slow queries.
Performance Tuning Basics
Practical techniques for diagnosing and fixing slow MongoDB queries, covering the profiler, index hygiene, schema design, and working-set memory.