MongoDB – Indexes and the ESR Rule

Without an index, answering a query means reading every document in the collection. With the right one, MongoDB jumps straight to the answers. The gap between those two is the difference between an application that scales and one that falls over at a few hundred thousand documents.

Creating an index is one line. Creating the right one comes down to a rule about field order, and to reading one command’s output.

The kinds you will actually use

db.reels.createIndex({ slug: 1 }, { unique: true })          // single field, unique
db.reels.createIndex({ status: 1, publishedAt: -1 })          // compound
db.reels.createIndex({ tags: 1 })                             // multikey (tags is an array)
db.reels.createIndex({ title: "text" })                       // text
db.view_events.createIndex({ ts: 1 }, { expireAfterSeconds: 7776000 })   // TTL

1 is ascending, -1 descending. On a single-field index the direction does not matter — MongoDB can walk it either way. On a compound index it matters a great deal, and that is the next section.

Three of these have a wrinkle worth knowing now. A multikey index is not a separate type you ask for: index an array field and MongoDB indexes one entry per element automatically, which is what makes { tags: "motorsport" } a seek. A unique index counts a missing field as null, so a second document without that field collides with the first — use a partial index to allow many. And a TTL index deletes documents rather than just indexing them, which is either exactly what you want or a surprise you will only have once.

ESR: the rule that decides field order

A compound index is one ordered structure, not several indexes bolted together. Its usefulness depends entirely on the order of its fields, and the rule is Equality, Sort, Range: fields matched exactly go first, then fields you sort by, then fields matched as a range.

ReelCMS’s feed query is the worked example. It filters on an exact status and sorts by date:

db.reels.find({ status: "PUBLISHED" }).sort({ publishedAt: -1 })

Equality on status, sort on publishedAt — so the index is declared in that order:

reels.createIndex(new Index().on("status", Sort.Direction.ASC).on("publishedAt", Sort.Direction.DESC));

Reverse those two fields and MongoDB can still use the index — but it can no longer supply the ordering, so it has to sort the matched documents afterwards, in memory. That path is capped at 32 MB and fails the query outright once the result set exceeds it. An index that works in development and errors in production is usually this.

Reading explain()

explain() is how you stop guessing. The useful mode is "executionStats", which runs the query and reports what it actually did:

db.reels.find({ status: "PUBLISHED" }).sort({ publishedAt: -1 }).explain("executionStats")

Run against ReelCMS, the parts that matter come back like this:

{
  "winningPlan":  { "stage": "FETCH", "inputStage": { "stage": "IXSCAN",
                    "indexName": "status_1_publishedAt_-1" } },
  "executionStats": { "totalKeysExamined": 12, "totalDocsExamined": 12, "nReturned": 12 }
}

Three things to check, in order of importance:

1. IXSCAN, not COLLSCAN. COLLSCAN means every document was read. On a small collection it is fine and fast; it is also a time bomb.
2. No SORT stage. A SORT above the scan means the index did not supply the ordering — the 32 MB problem above. Here there is none: the plan is FETCH over IXSCAN and nothing else.
3. keysExamined ≈ nReturned. This is the quality measure, and the one people skip. Twelve keys examined, twelve documents returned: a perfect ratio, nothing read and thrown away. Examine ten thousand keys to return ten documents and the index is being used but is the wrong shape.

That third ratio is the number to watch as data grows. It degrades quietly, long before anything times out.

The prefix rule — why you need fewer indexes than you think

A compound index serves any query that uses a left-hand prefix of its fields. One index on three fields therefore covers three cases:

db.reels.createIndex({ "creator._id": 1, status: 1, publishedAt: -1 })

db.reels.find({ "creator._id": id })                                  // ✓ uses it
db.reels.find({ "creator._id": id, status: "PUBLISHED" })             // ✓ uses it
db.reels.find({ "creator._id": id, status: "PUBLISHED" }).sort({ publishedAt: -1 })  // ✓ uses it

db.reels.find({ status: "PUBLISHED" })                                // ✗ skips the first field

The last one cannot use it. An index is ordered by its first field, then its second within that, and so on — skipping the leading field is like looking up a surname in a phone book indexed by first name. That is why ReelCMS has a separate {status, publishedAt} index even though those fields already appear inside the three-field one.

Practically: create indexes for query shapes, not for fields, and check whether a shape is already a prefix of something you have before adding another. Two well-ordered compound indexes routinely replace five single-field ones.

Covered queries

If an index contains every field a query needs, MongoDB can answer from the index alone and skip reading documents entirely. The plan shows PROJECTION_COVERED and no FETCH:

db.reels.createIndex({ status: 1, publishedAt: -1, slug: 1 })
db.reels.find({ status: "PUBLISHED" }, { slug: 1, _id: 0 }).sort({ publishedAt: -1 })
// totalDocsExamined: 0

Note _id: 0. Without excluding it the query needs a field the index does not carry, and the coverage is lost. This is worth reaching for on a hot endpoint that returns a few fields from large documents; it is not worth distorting an index to achieve.

Partial and sparse

A partial index covers only documents matching a filter. It is smaller, cheaper to maintain, and the correct way to make “unique when present” work:

db.reels.createIndex(
  { publishedAt: -1 },
  { partialFilterExpression: { status: "PUBLISHED" } }
)

The catch is that MongoDB will only use it when the query provably stays inside the filter — a query with no status condition cannot use the index above, even if every matching document happens to be published. Prefer partialFilterExpression to the older sparse option, which is a blunter version of the same idea.

Building indexes without an outage

Two practical points about production. Index builds since MongoDB 4.2 do not block reads and writes for their duration, but they do consume I/O and memory, so a build on a large collection is still a maintenance-window decision. And every index makes writes slower and takes memory — indexes are not free, and an unused one is pure cost:

db.reels.aggregate([{ $indexStats: {} }])   // usage count per index, since server start
db.reels.dropIndex("tags_1_publishedAt_-1")

$indexStats is the one to run before a tuning session. An index with an accesses.ops of zero after a week of production traffic is a write penalty you are paying for nothing.

One last thing about where indexes are declared. ReelCMS puts every one of them in a single class rather than scattering @Indexed annotations across its entities, and turns Spring Data’s auto-index-creation off. The reason is operational: an annotation that silently triggers an index build against a live collection is a production incident that starts as a one-line diff. Having the whole index strategy in one file is also the only way anyone ever reviews it.

The short version

• Compound index field order follows Equality, Sort, Range.
• Wrong order still uses the index but sorts in memory — capped at 32 MB, then it fails.
• explain("executionStats"): want IXSCAN, no SORT, and keysExamined close to nReturned.
• Indexing an array gives you a multikey index automatically.
• A unique index treats missing as null; use partialFilterExpression for “unique when present”.
• Every index slows writes. Check $indexStats and drop the unused ones.