"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.
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
A single index can cover multiple fields, but the order those fields appear in isn't arbitrary. The widely accepted rule of thumb is ESR: 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.
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 — 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.
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.
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 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.
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.