4. Compound Indexes and Field Order
A compound index contains multiple fields in a defined order. It can support queries that filter or sort using compatible combinations of those fields.
For example, { status: 1, createdAt: -1 } orders index entries first by status and then by creation time within each status value. This can support eligible queries that filter by status and sort by creation time.
Field order matters. { status: 1, createdAt: -1 } is not equivalent to { createdAt: -1, status: 1 }. The first index is organized primarily by status; the second is organized primarily by date.
Compound indexes can support multiple query shapes, but only when the query predicates and sort pattern align with the index ordering. Avoid creating a compound index by merely listing every field that appears in an application model.
A compound index with fields in the wrong order may not efficiently support the intended filter or sort. Design from the query pattern, then verify with explain.
db.orders.createIndex({ status: 1, createdAt: -1 });
db.orders.find({ status: "delivered" })
.sort({ createdAt: -1 })
.limit(20)
.explain("executionStats");- Compound indexes index fields in a specific sequence.
- The leading field strongly influences which query shapes the index supports.
- Index order affects filter and sort support.
- Use actual query patterns to choose field order.
What does the index { status: 1, createdAt: -1 } order first?