A compound index is one index over several fields. People often picture it as “an index on a and an index on b glued together”. It isn’t. It’s a single sorted list of keys, and the order you list the fields in when you create it decides which queries it can help. Get that order right and one index serves several queries. Get it wrong and it does almost nothing.
The commands below are mongosh for MongoDB 7 and 8. No server was available while writing, so outputs are illustrative of documented behaviour.
Think of a phone book
A phone book is sorted by last name, then by first name within the same last name. That’s exactly a compound index on { last: 1, first: 1 }.
With the phone book you can quickly find:
- everyone named Patel (one contiguous block),
- Patel, Riya (a smaller block inside it),
- Patels whose first names start with R (a range inside the block).
You can’t quickly find everyone whose first name is Riya. They’re scattered across every last name. You’d have to read the whole book.
That’s the prefix rule. An index on { a: 1, b: 1, c: 1 } can support queries on a, on a and b, and on a, b and c. It can’t efficiently support a query on b alone or c alone, because those values aren’t contiguous in the index.
A worked example
An orders collection where support staff look up a customer’s orders by status, newest first:
db.orders.find({ customerId: 42, status: "shipped" }).sort({ createdAt: -1 })Suppose you create this index because it “has all three fields”:
db.orders.createIndex({ createdAt: -1, customerId: 1, status: 1 })It’s sorted by date first. Customer 42’s shipped orders are spread across the whole date range, mixed in with everyone else’s. MongoDB can walk the index in date order (so it avoids a sort), but it has to look at keys for every order from every customer to find the ones for customer 42. On a collection with 20 million orders, that’s 20 million keys examined to return perhaps 15 documents.
Now order the fields differently:
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })The index jumps straight to the block for customer 42, then to shipped within it, and the keys inside that block are already in createdAt descending order. It reads 15 keys, fetches 15 documents and returns them without sorting. Same three fields, completely different cost.
You can check this with explain:
db.orders.find({ customerId: 42, status: "shipped" })
.sort({ createdAt: -1 })
.explain("executionStats").executionStats
// { nReturned: 15, totalKeysExamined: 15, totalDocsExamined: 15, ... }When nReturned, totalKeysExamined and totalDocsExamined are all about equal, the index is doing its job.
The ESR rule: equality, sort, range
The guideline MongoDB documents for ordering fields is ESR:
- Equality fields first: fields matched with an exact value (
customerId: 42). - Sort fields next: the fields in your
.sort(). - Range fields last:
$gt,$lt,$inwith many values in some cases,$regexprefixes.
Why sort before range? Consider a slightly different query:
db.orders.find({ customerId: 42, total: { $gt: 100 } }).sort({ createdAt: -1 })If the index is { customerId: 1, total: 1, createdAt: -1 } (equality, range, sort), then within customer 42 the keys are ordered by total, and only within equal totals by date. The orders with total > 100 come out in total order, not date order, so MongoDB has to collect them and sort in memory. You’ll see a SORT stage in the plan.
If the index is { customerId: 1, createdAt: -1, total: 1 } (equality, sort, range), MongoDB walks customer 42’s keys in date order and skips keys whose total is 100 or less as it goes. It might examine a few extra keys (the cheap orders), but there’s no in-memory sort, and with a .limit(20) it can stop after 20 matches. For paginated screens, that’s almost always the better trade.
ESR is a guideline, not a law. If the range condition is extremely selective (say it filters out 99.9% of a customer’s orders) and there’s no limit, putting the range before the sort can win. That’s what explain is for. In an interview, stating ESR and then saying “I’d confirm with explain on real data” is the answer people want to hear.
Things that trip people up
Order in the query doesn’t matter. { status: "shipped", customerId: 42 } and { customerId: 42, status: "shipped" } are the same query to the planner. Only the index definition order matters. This is a common multiple-choice trick.
Sort direction matters for multiple sort fields. An index { a: 1, b: -1 } supports sort({ a: 1, b: -1 }) and its full reverse sort({ a: -1, b: 1 }), because an index can be walked backwards. It can’t support sort({ a: 1, b: 1 }). With a single sort field, direction doesn’t matter at all.
Too many indexes cost writes. Each index is updated on every insert and on updates to indexed fields. Rather than creating { customerId }, { customerId, status } and { customerId, status, createdAt }, create only the last one: its prefixes cover the first two queries. Redundant prefix indexes are one of the first things to look for when a write-heavy collection is slow.
Index intersection rarely saves you. MongoDB can in theory combine two single-field indexes, but the planner seldom picks that plan, and it never helps with sorting. Don’t design around it.
Interview practice
A product listing runs these three queries constantly:
db.products.find({ category: "shoes" }).sort({ price: 1 })
db.products.find({ category: "shoes", brand: "Asics" }).sort({ price: 1 })
db.products.find({ category: "shoes", price: { $lte: 80 } }).sort({ price: 1 })Design the fewest indexes that serve all three without an in-memory sort.
Answer
Start with what each query needs. All three filter on category by equality and sort on price. The second also has brand as an equality, and the third has a range on price, which is the same field as the sort.
{ category: 1, price: 1 } serves the first query perfectly: jump to shoes, read in price order. It serves the third too, because the range and the sort are on the same field: MongoDB jumps to shoes and reads keys up to 80 in price order. That’s a tight index scan with no sort.
The second query is the problem. With { category: 1, price: 1 }, MongoDB reads all shoes in price order and filters out non-Asics documents after fetching them. No sort is needed, but it examines far more documents than it returns. Following ESR, the ideal index is { category: 1, brand: 1, price: 1 }. That index can’t serve the first and third queries well, though, because price isn’t directly after category in it: MongoDB would have to read every brand’s block and merge or sort them.
So the answer is two indexes: { category: 1, price: 1 } for queries one and three, and { category: 1, brand: 1, price: 1 } for query two. A strong candidate adds that if brand filters are rare or there are only a few brands per category, the single index might be good enough, and the way to decide is to compare totalDocsExamined against nReturned in explain on production-shaped data.