MongoDB Optimization: Where Queries Lose Speed
Slow queries in MongoDB rarely appear immediately: a service runs for months on a small collection where everything needed is read from memory.
The tipping point comes as data grows — a query that responded instantly yesterday starts hitting the query planner, and response time grows along with collection size.
The first source of loss is collection scanning instead of index access. explain() shows COLLSCAN where IXSCAN was expected, and from there each new batch of documents makes reads more expensive.
Sometimes an index formally exists, but the compound index is built in the wrong order — equality, sort, range — and the planner simply does not use it.
Second — sorts and the aggregation pipeline. A sort without index support runs in memory, and when it exceeds the limit it spills to disk; intermediate stages accumulate document sets when filtering and projection come after collection joins rather than before them. Every extra stage means additional reads and extra disk load.
Third — repeated disk queries. Without a covering index, the same documents are read again and again, the working set no longer fits in memory, cache is evicted, and disk I/O becomes the bottleneck of the entire database. Below we break down how this is addressed.
What Index and Execution Plan Tuning Delivers
We start work on data access with the execution plan: explain in statistics mode shows how many documents were examined versus returned, whether the query used an index or went into a full collection scan, and where the sort exceeded the memory limit.
A gap of tens of times between these numbers is a normal starting point, and it translates directly into response time: extra documents are read from disk rather than from cache.
Next we rebuild indexes for actual query patterns.
A compound index is built in the order "equality → sort → range" so that the filter and sort are covered in a single pass; where the application selects only a few fields, we make it covering — the server returns the response without accessing the documents themselves.
In parallel, we remove duplicate and unused indexes: every extra index slows writes and inflates the working set, evicting needed pages from memory. For MongoDB, this is not a one-time fix but a reconciliation with the current query profile.
The effect is visible in numbers: response time on hot queries is reduced severalfold, the share of disk reads drops from tens of percent to single digits, and the most unpleasant class of failures — timeouts under peak load — disappears because the query stops holding locks and an execution queue as the number of concurrent calls grows.
Stability under load matters more than average time: a system that maintains response during a tenfold spike does not drop user scenarios during peak hours.
The execution plan is not a constant: the collection grows, filters change, and the optimizer may choose a different strategy. That is why we set up regular slow query monitoring and alert thresholds so that degradation is caught before complaints, not after.
Approaches to Query Acceleration: Comparing Options
The first step is to capture the query execution plan: it shows whether the query uses an index or the collection is scanned in full, and how many documents were examined for just a few needed ones.
Then the choice is between several strategies, and they are not interchangeable: each accelerates its own class of queries and hits its own limit.
| Option | When it is appropriate | Limitations |
|---|---|---|
| Single-field point index | Filter by a highly selective field, returning only a few documents | Sorting by a second field goes to memory and breaks the query on a large dataset |
| Compound index | A stable set of conditions plus sorting by the same fields | Field order is rigid: a query with a different set of fields will not use the index |
| Covering index | Filter and projection are entirely in the index, the document is not read | Index size and the cost of each write grow; the field list is hardcoded into the index |
| Read separation | Reporting and analytical queries that must not interfere with the main workload | Secondary node lag and relaxed consistency; the query itself is not accelerated |
| Data separation | Heavy embedded arrays that are rarely read but bloat the document | Writes and consistency become more complex, and additional lookups are added |
In practice, options are combined: a compound covering index handles regular reporting, while heavy exports are moved to a separate read path.
We discuss the limitations of each step before implementation — otherwise accelerating queries results in increased write time and desynchronization between nodes.
Work Stages: From Query Audit to Handover of Settings
Optimization begins not with creating indexes but with an inventory: we need to understand which database calls consume time and why. Otherwise a fix turns into a guess that speeds up one query and imperceptibly slows down ten others.
-
Query profiling — enable slow operation collection and group them by common pattern. This reveals stable bottlenecks rather than isolated outliers; application and analytics calls are marked separately because they have a different cost of error.
-
Plan analysis — for each pattern, we look at which index the planner chooses, at which stage documents are lost, and where a full collection scan occurs instead of a key lookup.
-
Index and access pattern fixes — we build compound indexes for the actual field order in the filter and sort, and add covering indexes where the application fetches only part of the fields.
In the aggregation pipeline, we move filtering to the beginning, before grouping and array unwinding.
-
Data model review — nesting and duplication: sometimes a change to the document structure removes an entire batch of heavy calls.
-
Load testing — we run the changed variant against a realistic profile: the same read/write ratio, the same competing operations. We compare the plan, number of examined documents, and response time before and after.
-
Documentation and handover of settings — we provide a list of indexes with rationale, rules for accessing collections, monitoring metrics, and short instructions so the team can maintain the result without us.
Indexes, Schemas, and Policies: What Remains in the Project
Query optimization lives not in a report but in repository artifacts. If the index composition and access rules remain only in an engineer's head, the next developer will add a duplicate index or remove a needed one — and degradation will return within a couple of releases.
- Index descriptions — composition and field order, selectivity, and which read scenario it was created for. Creation is done through a migration, not a manual command on a production server.
- Collection schemas and validators — required fields, allowed types, prohibition of heterogeneous structures in one collection so that queries remain predictable.
- Data access rules — where reads go only through an indexed field, which queries without a filter are considered defects, and which operations are allowed on the hot path.
- Access policies and roles — separate accounts for services, minimally required privileges, and no administrative rights for the application.
- Maintenance policy — how often execution plans are captured, at what deviation from the baseline a query is sent for revision, and who checks it.
- Baseline plans and control queries — a set of queries with expected behavior used to check for regressions after schema changes.
- On-call instructions — what to look at when latency grows, which metrics to check, and in what order.
All artifacts are versioned alongside the code and handed over to your team. A month after handover, a new engineer will add an index without breaking existing ones, and the on-call engineer will find the cause of degradation using the instructions — without contacting us.
Case Study: Accelerating a Catalog Without Rewriting Logic
What Was the Situation
The catalog grew from several thousand items to tens of thousands, and response time for category listing degraded: at peak it reached three to four seconds, the 95th percentile went past six, and some queries failed due to timeout. The query profile showed two causes.
Filters by category, brand, and availability ran without a compound index, so every query read the entire collection, and the aggregation pipeline for the storefront first joined related documents across the whole catalog and only then cut off the excess with a filter.
What We Changed
We analyzed the execution plan of real queries on MongoDB: it was clear where a full collection scan occurred and where an index existed but was not used due to field order.
We built compound indexes for the actual filter combinations: category and availability as leading fields, price and date at the tail for sorting.
In the pipeline, we moved filtering before joins with related collections and kept projection to only the needed fields — the second stage receives dozens of documents instead of the entire catalog. We did not touch the listing logic or response format.
What the Result Was
Response time decreased by about eight times, the 95th percentile returned to fractions of a second, and timeouts disappeared. The volume of data read by a single query dropped by an order of magnitude — with the same number of products and without rewriting the logic.
The catalog has grown further since then, but response time has remained the same: the headroom is maintained because every new filter goes through the same plan analysis before release.
What to Do If MongoDB Optimization Did Not Help
The changes are in place, indexes are built, but queries are still slow — meaning the bottleneck is not where it was sought.
The second pass starts not with new indexes but with analyzing the plans of existing ones: the MongoDB planner shows whether the index is used or the collection is read with a full scan.
The field order of a compound index is the first common mistake. An index accelerates a query only if its prefix matches the filter conditions; if a low-selectivity field comes first or the fields are reordered, the planner rejects the index. Reordering fields is a configuration change, and the gain is measured in multiples.
Resource contention produces a similar picture: a fast query on a loaded node waits in queue, long operations hold locks and push others out.
We limit execution time and move heavy aggregations to a separate path — response returns to normal without rewriting the queries themselves.
Poor data distribution across nodes concentrates all load on one while the others sit idle.
Rebuilding the shard key or moving a growing collection to a separate path removes the skew, but requires a migration plan and a tested rollback — otherwise the fix will be more expensive than the original problem.
A repeat audit is not a one-time fix but a cycle: measurement, hypothesis, change, measurement again under production load. Without it, optimization remains a set of guesses that can neither be confirmed nor refuted.
Technical Doubts: Answering Common Questions
Query optimization runs into not only the query itself but also what surrounds it: how changes will affect writes, whether the document schema needs to be touched, what will change with sharding, and where to verify the result. Below are the questions most often received during the assessment stage.
Will new indexes slow down writes?
They will, and this is factored into the calculation. Every insert and update maintains all indexes in the collection, so we look at the read/write ratio and the index size in the working set.
Indexes that usage statistics do not support are removed — usually it is they that create extra write load.
Can we avoid changing the schema?
In most cases, yes. Field order in a compound index, partial and TTL indexes, and a rewritten sequence of aggregation stages provide gains without changing documents.
We suggest changing the structure only where rebuilding the query does not solve the task — and then with backward compatibility and a migration plan.
What happens with sharding?
The shard key determines whether a query hits one shard or fans out across all of them. If the query condition does not include the key, part of the work is wasted.
We check execution plans, add the key to the index prefix, or adjust the query condition so that routing is precise.
In what environment do we verify the result?
Only on a comparable data volume: on an empty database the planner will choose a different plan, and the measurements will be false.
We deploy a copy with an anonymized slice, capture plans before and after, and record response time and the number of examined documents — this is the proof of the effect.
Let's Discuss Your Task: We Will Return with an Estimate and Work Plan
MongoDB optimization begins not with fixing individual queries but with a picture of the workload: which queries are slow, which indexes are actually used, and where aggregation pipelines scan the collection in full.
To return with a scope estimate and a stage plan, we need just a few artifacts — an on-call engineer can collect them in one pass through the system.
- List of problematic operations: response time and call frequency over 24 hours.
- Explain output for them — execution plan, share of documents filtered out, index used.
- Collection schema: field types, volumes, distribution across replicas.
- Current indexes and their access statistics — you can immediately see which ones are idle.
- Workload profile: read/write ratio, peak hours, nature of spikes.
- Environment constraints: maintenance window, available replicas for heavy tasks, data consistency requirements.
In response, you will receive not a list of generic recommendations but a stage plan with priorities: what delivers an effect immediately, what requires index rebuilding and preparation, and which metrics we use to measure the result.
Then we will agree on the order and start with the area where the return is highest — describe your task, and we will return with an estimate.