MongoDB compound indexes: the field order that makes or breaks them

A compound index indexes more than one field at once, and the order you list those fields in is the whole game. Same fields, different order, completely different index. Get the order right and a single compound index serves a filter and a sort in one pass. Get it wrong and MongoDB quietly ignores the index and scans anyway, which is the exact outcome you built the index to avoid.

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

Here is how to order the fields, and why the order has the power it does.

The rule: Equality, Sort, Range

The field order that works follows one rule, Equality, Sort, Range:

• Equality first: fields you match on an exact value (userId: 123, status: "shipped").
• Sort next: the field you order results by (createdAt).
• Range last: fields you filter with a range ($gt, $lt, $in, $gte).

So a query like this:

db.orders
  .find({ userId: 123, status: "shipped", total: { $gt: 5000 } })
  .sort({ createdAt: -1 })

wants this index:

db.orders.createIndex({ userId: 1, status: 1, createdAt: -1, total: 1 })
//                       equality    equality    sort         range

With the fields in that order, MongoDB walks the index straight to the matching rows, in the sort order you asked for, and never loads documents into memory to sort them. Put total (a range) before createdAt (the sort) and it cannot do that, because a range scan scatters the sort order. The MongoDB manual calls this the ESR guideline, and it is the single most useful thing to know about compound indexes.

The prefix rule: why one index does the work of three

A compound index also serves queries on its leading fields, its prefixes. An index on { userId: 1, status: 1, createdAt: -1 } supports:

• queries on userId alone
• queries on userId and status
• queries on all three

but not on status alone, or createdAt alone. The leading field has to be involved. This is why you can often delete a pile of single-field indexes: one compound index on { a, b, c } covers { a } and { a, b } for free. Three indexes become one, with the same coverage, less write cost, and less storage. Finding those redundant single-field indexes is part of index optimization.

Sort direction matters here (and only here)

On a single-field index, direction barely matters, MongoDB can read it either way. On a compound index it matters, because the directions have to line up with a multi-field sort. An index on { status: 1, createdAt: -1 } serves sort({ status: 1, createdAt: -1 }) and its exact reverse, but not sort({ status: 1, createdAt: 1 }). If you sort two fields in opposite directions, the index directions have to match.

Confirm it worked

Built the index, still slow? Check the plan:

db.orders.find({ userId: 123, status: "shipped" })
  .sort({ createdAt: -1 })
  .explain("executionStats")

An IXSCAN with totalDocsExamined close to nReturned means the compound index is doing its job. A COLLSCAN, or an IXSCAN followed by an in-memory SORT stage, means the field order is off. Reading that plan is covered in why your MongoDB query is slow, and the basic index syntax is in how to create an index.

mongoui's Query Optimizer reads that explain() output and tells you, in plain English, whether your compound index matched and what order it should be in if it did not. Its Index view shows the prefix overlaps, so the three-single-indexes-become-one cleanup is obvious. Local narration makes no provider call. Explicit AI narration sends the authored query and bounded structural plan context directly to your selected provider; full documents and MongoDB credentials stay local. Order the fields Equality, Sort, Range, and let one index cover what three used to.