How does field order in a compound index affect which queries can use it?

Short answer

A compound index serves queries that use a prefix of its fields, left to right. { a: 1, b: 1, c: 1 } serves a, a+b, and a+b+c, but not b alone.

Why?

ESR is the usual design rule: equality fields, then the sort field, then range fields. Putting sort after a range forces an in-memory sort. Equality on a suffix field cannot jump into the middle of the tree.

Example

db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

db.orders.find({ userId: id, status: "delivered" }).sort({ createdAt: -1 })
// uses the index

db.orders.find({ status: "delivered" })
// cannot use it as a leading key

Interview tip

Name ESR and the prefix rule. That is a senior-level indexing answer.

Common mistake

Creating { createdAt: -1, userId: 1 } for a query that always filters userId first.