A 180 ms product query and a 40-minute nightly report
An orders collection holds 12.4 million documents. The customer page runs db.orders.find({customerId: "c-8842", status: "SHIPPED"}).sort({createdAt: -1}).limit(20) and takes 180 ms. explain("executionStats") reports keysExamined 41,200, docsExamined 41,200, and a blocking SORT stage. The only useful index is {customerId: 1, status: 1}. Separately, a nightly report matches {status: "SHIPPED"} - about 4.1 million documents - sorts by createdAt: -1, and streams every field of every matching document. It uses the index {status: 1, createdAt: -1} and still runs 40 minutes. A teammate proposes replacing both indexes with a single index: {status: 1, customerId: 1, createdAt: -1}.
1. Evaluate the proposed single index. What index would you build for the customer-page query, and would the proposal also fix the nightly report?
Read the worked answer
Apply the equality-sort-range rule: equality fields first (in any order among themselves), then the sort key, then any range field. The endpoint has two equalities (customerId, status) and one sort (createdAt), so {customerId: 1, status: 1, createdAt: -1} satisfies it, and so does the proposed {status: 1, customerId: 1, createdAt: -1} - both have the equalities before the sort key. With a matching index the query walks the index in createdAt order, stops after 20 keys, and the SORT stage disappears. Sort direction is irrelevant here because there is only one sort field; an index can be walked backwards. Direction would only matter if the sort had multiple fields (then they must be all-ascending or all-descending).
The proposal does not fix the report. With status as an equality prefix, the next index field is customerId, so within status: "SHIPPED" the index is ordered by customer, then by date. The report needs global createdAt order, which this index cannot supply without a blocking sort. The existing {status: 1, createdAt: -1} is already the right shape for the report. Keep it (or a partial variant) and add the endpoint index; do not try to serve both with one compound index.
2. The nightly report already uses a well-shaped index, yet takes 40 minutes. What is actually costing the time, and what are two concrete fixes?
Read the worked answer
The index shape is fine, so the cost is not sorting - it is volume. 4.1 million index keys are traversed and 4.1 million full documents are fetched from disk, converted from BSON, and sent over the socket. The SORT is not the problem; docsExamined = 4.1M is.
Fix 1 - make it covered. If the report only needs status and createdAt (plus a computed count), project {_id: 0, status: 1, createdAt: 1}. Both fields are in {status: 1, createdAt: -1}, so the planner can answer entirely from index keys with zero document fetches. That is usually the single largest win here.
Fix 2 - shrink the index to only the rows that matter. A partial index on {createdAt: -1} with partialFilterExpression: {status: "SHIPPED"} contains only the shipped orders, is far smaller, and still provides the needed sort order. The caveat is that the query predicate must be compatible with the filter expression for the planner to use it.
A third option, if the report is truly a full export, is to batch by _id range rather than one long cursor, so a single pass does not hold a snapshot and flood the network. Verify the outcome with explain("executionStats"): totalDocsExamined should drop to zero for the covered case.
3. The endpoint's explain output shows keysExamined 41,200 with the existing two-field index. Walk through what would change in the execution stats after your fix, and name one metric that would still look bad.
Read the worked answer
Before: the planner scans the {customerId: 1, status: 1} index for the one customer (41,200 entries, many not SHIPPED), fetches each matching document, throws them into a blocking SORT whose memory use grows with the match count, sorts, then takes 20. Hence keysExamined = docsExamined = 41,200 and a SORT stage.
After adding {customerId: 1, status: 1, createdAt: -1}: the same equality prefix seeks to the customerId + status: "SHIPPED" region, the index supplies createdAt descending order, and the engine walks 20 entries and stops. Expect roughly keysExamined: 20, docsExamined: 20, nReturned: 20, and a SORT stage replaced by an IXSCAN with no blocking stage. executionTimeMillis drops to single digits.
One metric can still look bad: totalKeysExamined versus nReturned across the whole plan is fine, but if the query is not covered, docsExamined stays equal to nReturned - that is expected and not a defect. What you should keep watching is whether limit is pushed down. If a $or or a non-equality predicate is added later, the planner may need to examine many more keys than 20 before finding 20 survivors, and totalKeysExamined >> nReturned is the signal that the index no longer matches the query shape.