The ESR Rule: How to Build the Perfect Compound Index in MongoDB

You added an index. You even checked twice that it exists. And the query is still crawling.

If you've ever been in that spot, the problem usually isn't that you're missing an index — it's that the fields inside your compound index are in the wrong order. MongoDB cares deeply about the order of fields in a compound index, and getting it wrong quietly kills performance while leaving the index sitting there looking innocent.

There's a simple rule that fixes most of these cases. It's called ESR: Equality, Sort, Range. Learn it once and you'll design better indexes for the rest of your career.

The query we're going to optimize

Let's use something concrete. Say you run a store and you have an orders collection. A very common query on your dashboard is:

Show me this customer's completed orders over $50, newest first.

In MongoDB that looks like:

db.orders.find({
  customerId: "u_8842",        // exact match
  status: "completed",         // exact match
  amount: { $gt: 50 }          // a range
}).sort({ createdAt: -1 })     // sort by date, newest first

Three filters and a sort. Nothing exotic. This is the kind of query that shows up on almost every real application, and it's the perfect thing to break down.

Now, which index should back it? Most people's first instinct is to just throw the fields in the order they appear in the query, or in the order they think of them:

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

Looks reasonable. It's also slower than it needs to be. Here's why.

What a compound index actually is

Picture a compound index as a sorted phone book, but with multiple columns. An index on { lastName: 1, firstName: 1 } sorts every entry first by last name, and then — only within each last name — by first name.

This has one huge consequence: the index is only sorted by a field if every field before it is pinned to a single value. First names are in alphabetical order, but only inside a given last name. Across the whole book, first names are all over the place.

That single fact is the entire reason ESR works. Keep it in your head as we go.

E — Equality first

Equality conditions (customerId: "u_8842", status: "completed") match one exact value. When these fields come first in the index, MongoDB can jump straight to the exact block of entries it needs and ignore everything else. It's the difference between opening the phone book to the right page versus reading it cover to cover.

So both of our exact-match fields go at the front:

{ customerId: 1, status: 1, ... }

The order between the two equality fields barely matters for this query — both narrow things down to a single value. In practice you'll order them based on which other queries you want the index to serve, but that's a refinement, not the core rule.

S — Sort second

Here's where most indexes fall apart.

After the equality fields have pinned customerId and status to single values, everything left in that slice of the index is — remember the phone book — sorted by whatever field comes next. If we make that field createdAt, then the entries are already in date order, sitting on disk exactly the way our .sort() wants them.

That means MongoDB can just walk the index and hand back results in order. No collecting-everything-then-sorting-in-memory. That step is free.

{ customerId: 1, status: 1, createdAt: -1, ... }

If you skip this and let the sort field come after a range field, MongoDB has no choice but to pull the matching documents into memory and sort them there. That's called a blocking sort, and it's exactly the thing you're trying to avoid. It's slow, it doesn't scale with your data, and there's a hard 100 MB memory ceiling on it — cross that and the query doesn't just slow down, it fails outright with a "Sort exceeded memory limit" error. Plenty of production incidents trace back to a query that worked fine until the dataset grew past that line.

R — Range last

Finally, the range: amount: { $gt: 50 }. Ranges go at the end.

The reason ties back to the phone book again. A range scans across many index entries rather than landing on one. And once you're scanning a span of values in a field, every field after it is no longer in any usable sorted order — the range "smears" the ordering of everything downstream. So if you put your range field before your sort field, you've broken the sort, and you're right back to a blocking sort.

Put the range dead last and it becomes a clean filter applied as MongoDB walks the already-sorted entries.

Putting it together

So the correct index for our query is:

db.orders.createIndex({
  customerId: 1,   // Equality
  status: 1,       // Equality
  createdAt: -1,   // Sort
  amount: 1        // Range
})

Equality, then Sort, then Range. Same fields as our first naive attempt — completely different behavior. This version narrows fast, returns results already sorted, and filters the range on the way through. No wasted work.

Proving it with explain()

Don't take my word for it — MongoDB will tell you exactly what it's doing. Run your query through explain("executionStats") and look at the winning plan:

db.orders.find({
  customerId: "u_8842",
  status: "completed",
  amount: { $gt: 50 }
}).sort({ createdAt: -1 }).explain("executionStats")

Two things to check:

The stage should say IXSCAN, not COLLSCAN. COLLSCAN means it's reading every document in the collection — your index isn't being used at all.

There should be no SORT stage in the plan. If you see one, MongoDB is doing a blocking sort in memory, which almost always means your sort field is sitting in the wrong place relative to a range. Reorder using ESR and it disappears.

While you're in there, glance at totalDocsExamined versus nReturned. If MongoDB examined 40,000 documents to return 20, the index is barely helping. With a good ESR index those two numbers get close, because the index does the narrowing instead of your query scanning and discarding.

The honest caveat

ESR is a rule of thumb, not a law of physics, and it's worth knowing where it bends.

When you put the range field last, MongoDB sometimes can't use it to tightly bound the index scan — it applies the range more like a filter than a hard boundary. So occasionally you're choosing between two things: an efficient sort with a looser range, or tighter range bounds with a blocking sort. ESR deliberately picks the first, because a blocking sort is usually the more expensive and more dangerous of the two (that 100 MB ceiling again). For the vast majority of queries that's the right call. But if you have a very selective range and a cheap sort, it's worth measuring both.

The other thing to remember is index prefixes. A compound index on { a, b, c } can also serve queries that filter on just a, or on a and b — reading left to right. It can't serve a query that filters only on b or only on c. So design your field order with your whole set of queries in mind, not just one. A well-ordered compound index often replaces three single-field ones.

The takeaway

Next time an index isn't pulling its weight, don't reach for a bigger machine or a second index — check the field order first. Line them up as Equality, Sort, Range, run explain(), and watch that SORT stage vanish.

Three letters. Most of your slow queries, gone.