A compound index in the wrong order is a sort you did not ask for

A compound index is not a set of fields. It is an ordered list, and the order decides which queries it can serve. The same three fields arranged two ways give you two different indexes, and one of them quietly leaves the server sorting in memory.

The rule has a name, ESR: equality, then sort, then range. It is easy to remember and easy to misapply, because the failure is silent. The query returns the right answer either way.

The query

db.events.find({ tenantId: 'acme', level: { $gte: 3 } })
  .sort({ createdAt: -1 })
  .limit(100);

Three fields, three roles:

• tenantId is an equality match. One exact value.
• createdAt is the sort.
• level is a range. Everything at or above 3.

The wrong order, and what it does

Group the fields the way they appear in the query and you get this:

db.events.createIndex({ tenantId: 1, level: 1, createdAt: -1 });

That index is used. explain() reports IXSCAN. It also reports this:

SORT
  └── FETCH
        └── IXSCAN  { tenantId: 1, level: 1, createdAt: -1 }

The SORT is the tell, and the reason it is there is worth following.

An index is a sorted list of keys. Walking { tenantId, level, createdAt } in order gives you everything for acme grouped by level, and within each level sorted by createdAt. That is not the same as sorted by createdAt. Level 3 has its own run of dates, level 4 has another, and they interleave.

Because level is a range rather than a single value, the walk spans many of those runs, so the server collects them all and sorts the result itself. On a large tenant that is a blocking stage over a lot of documents, and on MongoDB 6.0 and later it will spill to disk rather than fail, which means it gets slow instead of loud.

The right order

Put the sort field before the range:

db.events.createIndex({ tenantId: 1, createdAt: -1, level: 1 });

Now the walk is: pin tenantId to one value, and from there the index is already in createdAt order. The server reads keys in the order it needs, applies level as it goes, and stops after 100.

FETCH
  └── IXSCAN  { tenantId: 1, createdAt: -1, level: 1 }

No SORT. Same three fields, same query, one line different.

Why equality goes first

An equality field pins the walk to a single contiguous section of the index. Everything after it is then in order within that section, which is what makes the sort field usable.

Put the range first and you lose that: a range spans many sections, and nothing after it is in a single order any more. This is the same mechanism as the sort problem, one position earlier.

The order that works is therefore equality, then sort, then range, and the reason is not a convention. It is what walking a sorted list can and cannot give you.

Reading it in the plan

You do not have to reason it out each time. Run the query with explain('executionStats') and look for two things:

1. A SORT stage anywhere above the IXSCAN. If it is there, the index is not serving the ordering, whatever else the plan says.
2. totalKeysExamined against nReturned. With the wrong order the server reads far more index keys than it returns documents, because it is walking sections it will later throw away.

Both are visible in the explain visualizer, which draws the stage tree and suggests the compound index in ESR order for the query you pasted.

One caveat worth knowing

ESR is a default, not a law. When the range is very selective and the sort is over a small result, putting the range earlier can win, because the walk gets short enough that the in-memory sort costs less than the extra keys. That is a real case and it is rarer than people hope.

Measure before you break the rule. The plan will tell you which one you have.