C · Query performance
Design a compound index from a real query
Indexes should follow measured access patterns. This guide starts from an order-history screen, proposes one compound index, and shows how to test whether it actually reduces database work.
Start from the screen, not from the field list
Suppose an operations dashboard shows the newest 20 paid orders. Its query filters by one exact status value and sorts by createdAt and _id descending. That shape suggests an equality field first, then the sort fields.
db.orders.createIndex({ status: 1, createdAt: -1, _id: -1 })
db.orders.find(
{ status: "paid" },
{ customerId: 1, total: 1, createdAt: 1 }
).sort({ createdAt: -1, _id: -1 })
.limit(20)
.explain("executionStats")
The proposed order follows the equality-sort-range guideline for this query shape. It is a hypothesis, not a guarantee. The planner considers the collection, available indexes, and query details; it may choose a different plan when the data makes that cheaper.
Read the plan with a question in mind
explain("executionStats") gives you a plan and execution measurements. Start by asking how many documents the screen returns (nReturned) and how much work the plan performed (totalKeysExamined and totalDocsExamined). A query returning 20 documents after examining hundreds of thousands deserves investigation. Those numbers need context: a broad report may legitimately scan a lot, while a latency-sensitive point lookup should examine very little.
Look for a collection scan when you expected an index scan, but do not treat the stage name alone as a performance verdict. A tiny collection can be faster to scan than to seek an index. Compare plans against representative data and the production-shaped filter, sort, projection, and limit.
Compound indexes have a leading prefix
For an index { status: 1, createdAt: -1, _id: -1 }, queries beginning with status can use the index prefix. A query on createdAt alone cannot use the index as if it started with createdAt; it is missing the leading key. The index might still help other query shapes that include status, but the same index is not a universal speed switch.
The Equality-Sort-Range guideline is a starting point for choosing key order. Selectivity, requested sort, range predicates, and workload frequency matter together. A range field placed before a sort can make the requested sort harder to satisfy from the index. Measure a candidate against the actual query rather than memorizing one ordering as an absolute rule.
Tempting answers that fail
Adding an index for every filter can make reads look faster in a test while increasing storage and work for every insert, delete, and indexed-field update. Building an index with the right field names but the wrong leading order can leave a common query doing nearly the same scan. Neither createIndex() nor the presence of IXSCAN alone proves the application is faster.
Avoid copying a production query into hint() as a permanent fix before understanding why the planner chose another plan. A hint can be useful for controlled comparison, but a forced index can become a poor choice as data distribution changes.
Check your understanding
Question: With { status: 1, createdAt: -1 }, why is it risky to assume that a query filtering only on createdAt gets the same benefit?
Answer: Compound indexes are ordered by their leading fields. A query that omits the status prefix cannot narrow the index in the same way as the dashboard query. Inspect the plan and design for the actual access pattern.