Why Your MongoDB Query Is Slow And How To Tell

A MongoDB query that works fine on a thousand documents can quietly become a production incident at ten million. The reason is almost always the same: without the right index, MongoDB scans the entire collection to answer your question, and that cost scales linearly with data size while users wait. Indexing is the single highest-leverage skill for database performance, and it is more learnable than it first appears. An index is just a sorted data structure, a B-tree, that lets MongoDB jump straight to matching documents instead of examining every one. The hard parts are knowing which fields to index, in what order for compound queries, and how to read the explain output that reveals what the query planner actually did. Master those three things and the majority of your slow-query mysteries resolve themselves into clear, fixable patterns.

How Indexes Actually Work

An index in MongoDB is a B-tree storing the values of one or more fields in sorted order, with pointers back to the full documents. When you query a field that has an index, MongoDB walks the tree to find matches in logarithmic time instead of scanning every document linearly. This is why an indexed lookup on ten million documents can return in milliseconds while an unindexed one takes seconds. The trade-off is that every index consumes storage and must be updated on every insert, update, and delete that touches its fields. So indexes speed reads but slow writes and cost memory. The default index on the id field always exists. Beyond that, you add indexes deliberately, one per important query pattern, and you avoid indexing fields you never filter or sort on because those are pure overhead.

Reading Explain Output

The explain method is your primary diagnostic tool, and learning to read it ends most guessing. Run a query with explain set to executionStats and look at three numbers. The stage tells you the access method: COLLSCAN means a full collection scan and is usually bad, while IXSCAN means an index was used. The totalDocsExamined tells you how many documents MongoDB inspected, and the nReturned tells you how many it returned. The ideal ratio of examined to returned is close to one to one. If you return ten documents but examined a million, your index is missing or unselective. The totalKeysExamined shows index entries scanned. Compare these numbers before and after adding an index to prove the index helped. Never optimize blind; explain turns a vague feeling that a query is slow into a precise, measurable problem.

Compound Index Field Order

Compound indexes cover queries on multiple fields, but field order is everything and it follows the equality, sort, range rule. Place fields you match exactly first, then the field you sort on, then fields you query as a range. An index on status then createdAt efficiently serves a query that filters status equals active and sorts by createdAt, because the index is already sorted within each status value. Reverse that order and the index no longer supports the sort cleanly. A compound index can also serve queries on just its leading fields, a property called the prefix rule, so an index on a, b, c also helps queries on a alone or a and b together, but not on b alone. Designing one well-ordered compound index per query pattern beats scattering many single-field indexes that the planner cannot combine efficiently.

Covered Queries And Projections

A covered query is one MongoDB answers entirely from an index without ever touching the actual documents, and it is the fastest read possible. This happens when every field in your filter, sort, and projection exists in a single index, and you exclude the id field if it is not in that index. Because the index already holds those values in memory in sorted order, MongoDB never follows the pointer to fetch the full document from disk. In explain output, a covered query shows totalDocsExamined as zero while still returning results, which is the telltale sign. To exploit this, project only the fields you need rather than returning whole documents, and include those fields in the index. Covered queries shine on hot read paths like autocomplete or list endpoints where you return a few fields from a huge collection thousands of times per minute.

Index Selectivity Matters

An index only helps if it narrows results meaningfully, a property called selectivity. Indexing a boolean field like isActive, where most documents share the same value, barely helps because matching it still leaves MongoDB to examine half the collection. High-selectivity fields, like email or a user id, point to one or a few documents and make excellent indexes. When you must query a low-selectivity field, combine it in a compound index with a more selective one so the pair narrows results sharply. Before adding an index, ask how many documents a typical value matches; if the answer is a large fraction of the collection, the index will be ignored or barely used. The query planner itself estimates selectivity and may decide a collection scan is cheaper than a weak index, which is why a created index sometimes appears to do nothing.

The Working Set And Memory

MongoDB performance lives or dies by whether your working set fits in memory. The working set is the portion of data and indexes your application touches regularly. The WiredTiger storage engine caches frequently accessed pages in RAM, and as long as your hot indexes and documents fit there, reads are fast. When the working set exceeds available memory, MongoDB must fetch pages from disk, and latency climbs sharply and unpredictably. This is why a query can be fast in testing with a warm cache and slow in production under memory pressure. Keeping indexes small and selective directly reduces working set size, which is another reason not to over-index. Monitor cache usage and page faults. If performance degrades as data grows despite good indexes, you are likely outgrowing memory, and the fix is more RAM, smaller documents, or sharding rather than more indexes.

Avoiding Common Index Pitfalls

Several patterns silently defeat indexes. Regular expressions that are not anchored at the start of a string cannot use an index efficiently, so a leading wildcard search scans everything. Negation operators like not equal and not in tend to match most documents and rarely benefit from an index. Querying on a field with a different type than what is stored, like a numeric id sent as a string, misses the index entirely because the values do not compare equal. Sorting on a field absent from the supporting index forces an in-memory sort that fails past a memory limit on large result sets. Case-insensitive searches need a collation-aware index or they fall back to scans. The habit that protects you is running explain on every new query against production-scale data and confirming you see IXSCAN with a tight examined-to-returned ratio.

Partial And TTL Indexes

Beyond ordinary indexes, MongoDB offers specialized ones that solve specific problems cheaply. A partial index covers only documents matching a filter, so if you only ever query active users, indexing just those keeps the index small and reduces write overhead from inactive records. This is often better than a full index because it shrinks the working set. A TTL index automatically deletes documents after a set time based on a date field, which is perfect for sessions, caches, and ephemeral logs; a background thread removes expired documents without application code. Text indexes support keyword search, and sparse indexes skip documents missing the indexed field. Choosing the right specialized index can eliminate entire categories of maintenance work, like a cron job that purges old sessions. Match the index type to the data lifecycle, not just the query shape, and you write less code overall.