Indexing
Compound Indexes
Left-prefix matching, sort coverage, and why field order is the whole point.
A compound index is one tree on several fields, in declared order. { userId: 1, status: 1, createdAt: -1 } is not three indexes. Queries can use it only if they match a left prefix: userId, or userId+status, or all three. A query on status alone cannot walk this tree from the left.
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })
// uses the index
db.orders.find({ userId: ObjectId("64a1b2c3d4e5f6a7b8c90001") })
db.orders.find({
userId: ObjectId("64a1b2c3d4e5f6a7b8c90001"),
status: "delivered"
}).sort({ createdAt: -1 })
// cannot use this index
db.orders.find({ status: "delivered" })If the sort keys sit immediately after the equality fields, in the same order and compatible directions, the index can provide the sort — no in-memory SORT. { inStock: 1, price: 1 } serves find({ inStock: true }).sort({ price: 1 }). Reverse sort (price: -1) can still use it by walking the tree backwards when the rest matches. ESR (next) is the rule for mixing a range with a sort.
Interview question
How does field order in a compound index affect which queries can use it?
The index serves queries that use a left prefix of its fields. { userId: 1, status: 1, createdAt: -1 } serves userId, userId+status, and all three — not status alone. If the sort keys follow the equality fields, the index can also provide the sort. ESR is how you order equality, sort, and range.
Practice
Filter plus a sort — the compound shape, not two single-field indexes.