MongoDB indexes are
trickier than they look
“Just add an index” sounds like the simplest optimization there is. But how you build it, how you order
the fields, and whether it can satisfy a covered query — those details are what decide whether your query
runs in milliseconds or just gets lucky.
Why an index can make a query orders of magnitude faster
Picture an 800-page book with no table of contents — to find every place a keyword appears, you’d have to
flip from the first page to the last. That’s a
COLLSCAN
(collection scan). An index is that book’s table of contents: the query engine doesn’t flip through every
page, it jumps straight to the target. That’s an
IXSCAN
(index scan).
All 96 out of 96 documents get read, even though only 4 actually match
Only 4 out of 96 target documents are touched — the rest are never read
Field order decides whether an index actually helps
A single index can cover multiple fields, but the order those fields appear in isn’t arbitrary. The widely accepted rule of thumb isESR:Equality fields first,Sort fields second,Range fields last. Get the order wrong and the index still technically “exists” — the query planner just can’t put most of it to use.
{ createdAt: 1, status: 1 }
)
The sort field is listed first, but
status
is the equality filter in this query — the index can’t narrow down precisely first, so most of its
value is lost.
{ status: 1, createdAt: -1 }
)
status
— the equality match — comes first, then
createdAt
for sorting. The index can jump straight to the target range in one step.
Working out the right compound index by hand usually means reading through a wall of fields in an
execution plan. WAP’s real-time diagnostics compares slow queries against execution plans directly and
hands you a compound index recommendation you can use as-is.
The fastest query is the one that never reads a document
If every field a query needs — the filter, the sort, and the projected fields — happens to already be contained in the index itself, MongoDB doesn’t even need to fetch the full document. It returns the result straight from the index structure. This is called a covered query , and its latency typically runs another order of magnitude below a normal indexed query.
Normal Indexed Query
One extra disk / memory fetch
Covered Query
Lower latency · no document fetch
More indexes isn’t automatically better
Every new index adds a structure that has to be maintained on every single write — slower writes, higher
memory usage, a cost that’s easy to overlook. The goal of index design was never to “cover every query.”
It’s to cover the queries that are frequent and that actually matter.
Index fields ordered arbitrarily→Follow the ESR rule (Equality → Sort → Range)
→Audit cold indexes regularly with $indexStats
Index review is something that needs to happen continuously, and it’s exactly the kind of thing that
quietly falls off the list. WAP’s multi-dimensional monitoring keeps tracking index usage and slow-query
share, so “which indexes to clean up” stays visible instead of forgotten.
Getting indexes right is the highest
ROI optimization you can make
Most slow-query problems eventually trace back to an index that was never built correctly. Get that part
right, and let continuous monitoring and diagnostics handle the rest — that’s exactly what Whaleal
Platform is for.