MongoDB Indexing

The Equality · Sort · Range Rule

When building a compound index in MongoDB, the order of fields determines how efficiently your query runs. ESR is a rule of thumb for getting that order right — almost every time.

EEquality

→

SSort

→

RRange

What is a compound index?

A compound index covers multiple fields in one index. Unlike single-field indexes, the order of fields in a compound index physically determines how the data is sorted on disk — and that directly affects which queries can use the index efficiently.

// Single-field index — only helps queries that filter on "region" db.users.createIndex({ region: 1 }) // Compound index — can help queries that use region + name + joined together db.users.createIndex({ region: 1, name: 1, joined: 1 })

💡 A compound index is physically stored as a sorted B-tree. The first field determines the primary sort. The second field sorts within groups of the first. This is exactly why field order matters.

The three rules

Each type of query predicate behaves differently when MongoDB walks the index tree. Understanding how each one interacts with the index explains why ESR is the optimal order.

Equality — always first

Exact match filters like { status: "active" } or { region: { $eq: "AMER" } }

How it behaves: Equality creates tight index bounds — MongoDB jumps directly to the matching keys, like opening a dictionary to the right page. No scanning needed.

Why it goes first: It eliminates the most documents immediately. Every field after it only has to deal with the narrowed-down subset.

// All three are equality predicates find({ status: "active" }) find({ region: { $eq: "AMER" } }) find({ "address.city": "Chennai" }) // Index bounds: tight, single point indexBounds: { region: [ "[AMER, AMER]" ] }

Sort — always after equality

The .sort() clause or $sort stage in an aggregation

How it behaves: Sort fields need the full key range to be available — they produce unbounded index scans. But if they appear in the right position, MongoDB can use the index to deliver results in order without sorting in memory.

Why it goes second: After equality narrows the data, sort can use the index ordering. If sort comes after a range field, MongoDB can't use the index for ordering and must sort all results in RAM.

// Sort predicates find().sort({ name: 1 }) find().sort({ createdAt: -1, score: 1 }) // Index bounds: unbounded (scans full range) indexBounds: { name: [ "[MinKey, MaxKey]" ] } // But results come out in the right order — no memory sort!

Range — always last

Inequality filters like $gt, $lt, $gte, $lte, $in, $ne, $regex

How it behaves: Range creates loose bounds — MongoDB scans a portion of the key space rather than jumping to one point. After a range field, MongoDB can no longer use the index effectively for subsequent fields.

Why it goes last: Equality and Sort have already done the heavy filtering. The range does its partial scan on a small, already-narrowed set.

// Range predicates find({ age: { $gt: 25 } }) find({ price: { $gte: 100, $lte: 500 } }) find({ tags: { $in: ["js", "ts"] } }) find({ name: { $ne: null } }) // Index bounds: loose, scans a portion indexBounds: { age: [ "(25.0, inf.0]" ] }

Why field order changes everything

The index is a sorted list. The first field is the primary sort key. Swapping two fields produces a completely different physical order — and determines whether MongoDB can use the index efficiently or has to scan everything.

Example query

db.users.find({ region: "AMER" }).sort({ name: 1 })

5 documents: Shakir (AMER), Chris (AMER), III (APAC), Miguel (EMEA), Alex (AMER)

{ name: 1, region: 1 } Wrong order

#	name	region
1	Alex	AMER	match
2	Chris	AMER	match
3	III	APAC	miss
4	Miguel	EMEA	miss
5	Shakir	AMER	match

keys scanned

matched

AMER docs are scattered — MongoDB must read every single row to find them.

{ region: 1, name: 1 } Correct ESR

#	region	name
1	AMER	Alex	match
2	AMER	Chris	match
3	AMER	Shakir	match
4	APAC	III	stop
5	EMEA	Miguel

keys scanned

matched

All AMER docs grouped together. MongoDB jumps to row 1, reads 3, hits APAC and stops. Names are already sorted — no memory sort needed.

All three ESR scenarios

Each pairing of E, S, R has a specific reason for its ordering. Here's every combination you'll encounter in practice.

Equality then Sort (E → S)

Equality first ensures MongoDB skips to the right group; sort then uses index ordering within that group

db.users.find({ region: "AMER" }).sort({ name: 1 })

{ name:1, region:1 } wrong

Keys scanned5 of 5

Docs examined5

In-memory sortNo — but all scanned

Why badSort field is first → entire index scanned before region filter applies

{ region:1, name:1 } correct

Keys scanned3 of 5

Docs examined3

In-memory sortNone

Why goodEquality jumps to AMER group; names within AMER already sorted

Equality then Range (E → R)

Equality narrows the candidate set before the range scan even starts

db.users.find({ region: "AMER", joined: { $gt: 2015 } })

{ joined:1, region:1 } wrong

Keys scanned4 of 5

Docs returned2

Why badRange on joined scans 4 rows (2016–2018) then filters by region — wasteful

{ region:1, joined:1 } correct

Keys scanned2 of 5

Docs returned2

Why goodJumps to AMER block first; range scan only runs on 3 AMER rows

Sort before Range (S → R) — the trickiest one

Range before sort blocks index-based ordering, forcing an expensive in-memory sort

db.users.find({ joined: { $gt: 2015 } }).sort({ region: 1 })

{ joined:1, region:1 } wrong

Keys scanned4

In-memory sortYES — SORT stage

Why badIndex is sorted by joined, not region. After range scan, results are unordered — MongoDB sorts them all in RAM

{ region:1, joined:1 } correct

Keys scanned5 (1 extra)

In-memory sortNone

Why goodIndex ordered by region — results come out sorted. 1 extra key scan is far cheaper than sorting in memory

⚠ The in-memory sort is capped at 32MB by default. If your query returns large result sets and hits this limit, MongoDB throws an error. This is the most dangerous consequence of getting S and R in the wrong order.

Full E → S → R example

All three predicates in one query — the complete ESR rule in action

db.orders.find({
status: "shipped",
amount: { $gte: 100 }
}).sort({ createdAt: 1 })

{ status:1, amount:1, createdAt:1 } wrong

OrderE → R → S

In-memory sortYES

ProblemRange on amount breaks index ordering — sort can't use the index

{ status:1, createdAt:1, amount:1 } correct ESR

OrderE → S → R

In-memory sortNone

ResultEquality jumps to "shipped", sort uses index, range filters last

Index bounds for the correct ESR index

status

tight

createdAt

full range

amount

partial

Nuances and exceptions

The ESR rule is a guideline, not an absolute law. There are important edge cases to know so you don't blindly apply it.

📌

Multiple equality fields don't need a selectivity order

Old advice said to order equality fields from most selective to least selective. This is wrong. A B-Tree stores the same number of combinations regardless of order within equality fields.

// These two are equally efficient for // find({ country: "IN", status: "active" }) { country:1, status:1, ... } { status:1, country:1, ... } // same cost

⚡

$in counts as a range for sort purposes

$in is technically multi-equality, but for the purpose of sort ordering it behaves like a range. If you have a sort, put $in fields after the sort field.

// Query: find({ status:{$in:["a","b"]} }).sort({date:1}) // WRONG — $in before sort blocks index sort: { status:1, date:1 } // BETTER: { date:1, status:1 }

✓

Index prefix rule still applies

A compound index also serves any query that uses a prefix of its fields. An index on { a, b, c } can serve queries on just a, or a + b, but not b alone.

createIndex({ region:1, name:1, joined:1 }) // All these can use the index: find({ region: "AMER" }) find({ region: "AMER", name: "Alex" }) find({ region: "AMER" }).sort({ name: 1 }) // This CANNOT use the index efficiently: find({ name: "Alex" }) // skips first field

🔍

Always verify with explain()

ESR is a rule of thumb. The query planner has its own cost model and may choose differently. Always confirm with explain("executionStats").

db.orders .find({ status: "shipped", amount: {$gt: 100} }) .sort({ createdAt: 1 }) .explain("executionStats") // Look for: // - totalKeysExamined (low = good) // - stage: "SORT" (bad — in-memory sort) // - stage: "IXSCAN" only (good)

📊

Covered queries — add projection fields last

A covered query is one where MongoDB can answer entirely from the index without touching documents. To enable this, add extra projected fields at the end of the index — after ESR.

// Query: find({status:"active"}, {name:1, _id:0}) // .sort({createdAt:1}) // ESR + projection field at the end: createIndex({ status: 1, // E createdAt: 1, // S name: 1 // projected — now covered })

🚫

Reverse sort can still use the index

MongoDB can traverse an index backwards. sort({ name: -1 }) on an ascending index is fine. The problem is only when mixing directions on a multi-field sort that doesn't match the index.

createIndex({ region:1, name:1 }) // Both of these can use the index: find({ region:"AMER" }).sort({ name: 1 }) // forward find({ region:"AMER" }).sort({ name: -1 }) // backward // This CANNOT (direction mismatch): find({}).sort({ region:1, name:-1 }) // Fix: createIndex({ region:1, name:-1 })

✓ Quick mental test: For any compound index you're building, label each field as E, S, or R and check they appear in that order from left to right. If they don't, rearrange. Then verify with explain().

Quick reference cheatsheet

Common query patterns and their correct ESR index.

Common patterns

find({status:"A"}).sort({date:1})

{ status:1, date:1 }

E → S — equality then sort

find({status:"A", score:{$gt:5}})

{ status:1, score:1 }

E → R — equality then range

find({score:{$gt:5}}).sort({date:1})

{ date:1, score:1 }

S → R — sort before range, no E here

find({status:"A", region:"US"}).sort({date:1}).where(score > 5)

{ status:1, region:1, date:1, score:1 }

E + E → S → R — multiple equality, then sort, then range

find({status:"A"}, {name:1,_id:0}).sort({date:1})

{ status:1, date:1, name:1 }

E → S → projection field = covered query

Signs your index order is wrong

In explain() output

stage: "SORT" present
totalKeysExamined >> nReturned
memUsage growing large

At runtime

Queries are slow despite indexes
32MB sort memory exceeded
High CPU on query nodes