You run explain() on a slow MongoDB query and it shows IXSCAN, not COLLSCAN — so why is it still slow, and what does that tell you about the index you built?
Quick Answer
An IXSCAN (index scan) only proves an index was used, not that it was used efficiently — a query can scan thousands of index entries and still discard most of them if the index doesn't match the query's equality, sort, and range fields in the right order. The fix is almost always rebuilding the compound index using the ESR rule (Equality fields first, then Sort fields, then Range fields) so the index itself does more of the filtering.
Detailed Answer
Picture a phone book sorted by last name. If you search for everyone with last name 'Chen' born after 1990, using the phone book (the index) is obviously faster than reading every entry in the city (a collection scan). But if the phone book is instead sorted by birth year first, you'd have to flip through every single page checking for 'Chen' on each one — you're still using the book, just not efficiently, because the index's sort order doesn't match how you're actually searching.
MongoDB's explain("executionStats") reports a stage called IXSCAN when the query planner chose to walk an index rather than scan the whole collection (COLLSCAN). Teams often stop there, assuming 'IXSCAN means it's fixed,' but the real signal for efficiency is the ratio between totalDocsExamined (how many documents the index scan pulled up as candidates) and nReturned (how many of those actually matched the full query and were returned). A healthy index keeps that ratio close to 1:1; a ratio of 500:1 means the index narrowed the search barely at all before MongoDB had to fetch and manually filter hundreds of documents per result.
This mismatch happens because compound indexes (indexes on multiple fields) are ordered structures — MongoDB can only use an index's sort order left-to-right, meaningfully filtering on the leading fields and merely following the rest as a means to a sort or avoid a separate sort pass. This is why the ESR rule exists: put Equality-match fields first (so MongoDB jumps straight to the matching range), then Sort fields (so results already come out in the needed order without a separate in-memory sort), then Range fields last (since range conditions can only narrow, not jump to, a position in the index). An index built in the wrong order — say, range field first — forces MongoDB to scan a huge swath of the index checking each entry individually.
In production, engineers watch db.currentOp() and the slow query log (via profiling level 1 or 2) for exactly this totalDocsExamined-to-nReturned gap, not just the presence of COLLSCAN, because dashboards that only alert on 'any collection scan' miss the more common production issue of a technically-indexed but poorly-ordered query silently burning CPU and disk IO under real traffic. Index selectivity (how much a field narrows the result set) also matters — an index on a boolean 'is_active' field barely helps if 95% of documents share the same value, since the planner still has to examine a huge fraction of the index.
The gotcha that catches experienced engineers too: MongoDB's query planner caches the winning query plan and reuses it for subsequent similar queries, so a plan that was efficient when a collection was small can keep getting reused even after data distribution changes dramatically (for example, once 90% of orders become status: "completed" instead of an even split) — until the plan cache is invalidated by a write threshold or a manual db.collection.getPlanCache().clear(), the query keeps running the now-inefficient plan without any error, just quietly worse latency.