Design Indexes by Order and Range

In one line

An index is not a question of "which field to put it on" but of "in what order, and holding what". Even with the same three fields, the keys scanned differ by thousands of times depending on the order, and the size and lifetime change depending on the range it holds (partial, TTL). You judge by the numbers in the execution plan.

Why it was needed — slow even with an index

Most reports of "I added an index, so why is it slow" are not cases where the index is missing but where it does not fit. Take a common query on a list screen.

db.orders.find({status: "shipped", amount: {$gte: 150000}}).sort({createdAt: -1}).limit(20)

If you build the index {status, amount, createdAt} in the order the fields appear in the query, it narrows by status and then scans the amount range. But the result of scanning a range is not in createdAt order. So a SORT stage appears in the plan, and it takes the thousands of matching documents, loads them all into memory, sorts them, and then cuts 20. limit 20 does not help at all.

How it works — ESR

A compound index is a single list sorted by value, starting from the first field. That is where the ordering rule comes from.

Order	Field	Reason
E	Equality (status: "shipped")	Narrows to one range. Inside that range, entries are sorted by the next field
S	Sort (createdAt: -1)	Walking the equality range in its already-sorted order makes the sort free
R	Range (amount >= …)	Filters straight from the keys while walking. Stops when 20 are filled

If you build it as {status, createdAt, amount}, the plan becomes LIMIT>FETCH>IXSCAN, and the keys scanned shrink to just enough to fill 20. A field placed after a range field is used only for filtering, not for seeking — that is the entire difference between ERS and ESR.

There are three more.

• Covered query: if all the fields to return are inside the index, it does not open documents at all (totalDocsExamined: 0, PROJECTION_COVERED). If you forget to exclude _id in the projection, it opens documents to fetch _id.
• Partial index: holds only documents that match partialFilterExpression. For a screen that looks only for the 3% of unprocessed orders, the index becomes one thirtieth the size. In exchange, the planner can use it only if the query condition includes that expression. Leave status out of the condition and that index might as well not exist.
• TTL index: if you give a date field expireAfterSeconds, a background monitor deletes expired documents every 60 seconds. It looks only at the date type — a time written as a string is never deleted.

The order in which to read a plan

An execution plan is a tree. The outside (winningPlan) is what happens last, and following inputStage down brings you to what happens first. LIMIT>FETCH>IXSCAN is "scan the index → fetch the documents → cut at 20", and SORT>COLLSCAN is "scan everything → sort in memory". When reading, look at three numbers together.

Comparison	Meaning
totalKeysExamined ≫ nReturned	It used the index but the range is wide — suspect the order or a regex
totalDocsExamined ≫ nReturned	It fetches documents and then filters — the filtering field is not in the index
totalDocsExamined = 0	It answered from the index alone (covered)

The planner briefly runs the candidate plans side by side, picks the one that produced more results first, and then caches that choice per query shape. Losing plans remain in rejectedPlans. So creating a new index clears the cache, and the plan for the same query may change — which is why you measure again after a fix. To measure one specific index on its own, force it with hint, and to measure without an index, give {$natural: 1}.

What it looks like in the field

• A regex search narrows the index range only when it is a prefix starting with ^. /TN-123/ or the case-insensitive /^tn/i scan all the keys even when the plan says IXSCAN — which is why you must look at totalKeysExamined, not the stage name. Do case-insensitive search with a collation (strength 2) index, and that index can be used only by queries that give the same collation.
• Before deleting an index, try hiding it with hideIndex. A hidden index keeps being updated but the planner does not look at it, so you can measure the impact of deleting it while still being able to undo it. Conversely, if you forget it is hidden, you get the accident "the index is in the list, yet COLLSCAN".
• When you fix an index, speak in numbers, not "it got slow": keys scanned, documents opened, documents returned, and whether there is a SORT stage.

What you do in the next lab

On 60,000 orders, you measure the same list query with no index, with ERS, and with ESR and compare the numbers, then measure covered queries, partial indexes, TTL, and regex prefixes in turn. At the end, you take an incident where three screens got slow and tell apart the different causes behind the same symptom.