Orbit
Xstreami
A MongoDB database that is fast at 10,000 documents can become painfully slow at 10 million. The queries that worked during development start timing out in production. Collection scans replace index scans, aggregation pipelines choke on unfiltered stages, and end users see spinning loaders instead of search results. The root cause is almost never MongoDB itself — it is how the queries and indexes are structured against the data.
This is the story of how Mafiree's MongoDB consulting team reduced average API latency by 73% for a high-traffic e-commerce platform without adding a single server. If your MongoDB queries are slow and you need a systematic approach to MongoDB query optimization, this guide shares the exact diagnostic steps, fixes, and monitoring practices we used.
Why MongoDB Queries Slow Down
MongoDB query optimization challenges typically emerge when datasets outgrow the assumptions made during initial development. The symptoms are predictable, but the root causes are often buried deep in the interaction between query shapes, index coverage, and data modeling decisions.
Collection Scans (COLLSCAN)
The most common and most damaging issue. Without a matching index, MongoDB scans every document in the collection. On a collection with 50 million products, a single COLLSCAN can take seconds instead of milliseconds.
Inefficient Index Usage
Having indexes is not enough — the wrong indexes can be worse than none. A compound index on {category: 1, price: 1} is useless for a query filtering on {brand: 1, price: 1}. MongoDB's query planner selects the best available plan, but it can only optimize what exists.
Unbounded Aggregation Pipelines
Aggregation stages like $lookup and $unwind that run without an early $match stage force MongoDB to process the entire collection before filtering. This turns what should be a 5ms operation into a 5-second one.
Over-Fetching Documents
Queries that return entire documents when only two or three fields are needed waste network bandwidth, memory, and CPU cycles on deserialization.
For a deeper dive into query plan analysis, refer to MongoDB's official query optimization documentation.
The Client Challenge: An E-Commerce Platform Under Pressure
Our client operated a popular e-commerce marketplace built on MongoDB 6.0, running a 3-node replica set on AWS. The platform served 2 million daily active users, with a product catalog of 12 million documents and an orders collection exceeding 80 million documents.
Performance had degraded gradually over 18 months as the catalog grew. The symptoms were clear: product search API responses averaged 340ms (target: under 100ms), the checkout flow experienced intermittent timeouts during flash sales, and the analytics dashboard for sellers took over 8 seconds to load. The engineering team had added indexes reactively over time, resulting in 23 indexes on the products collection alone — many of which were redundant or unused.
Key Metrics Before & After Optimization
| Metric | Before | After |
|---|---|---|
| Avg. Product Search Latency | 340ms | 92ms |
| Checkout API (p99) | 1,200ms | 280ms |
| Seller Dashboard Load | 8.2s | 1.8s |
| Collection Scans / Hour | 4,200 | 12 |
| Active Indexes (Products) | 23 | 9 |
| Monthly AWS Spend | $4,800 | $3,200 |
Our 3-Step Diagnostic Process
Effective MongoDB query optimization starts with diagnosis, not guesswork. We follow a systematic three-step process that identifies the highest-impact fixes first.
Step 1: Profiler Analysis — Finding the Slow Queries
MongoDB's built-in database profiler captures queries that exceed a configurable threshold. We enabled profiling at level 1 (slow operations only) with a 100ms threshold to capture the worst offenders without generating excessive overhead.
// Enable profiling for operations slower than 100ms
db.setProfilingLevel(1, { slowms: 100 })
// Query the profiler for the top 10 slowest operations
db.system.profile.find({
op: { $in: ["query", "command"] },
millis: { $gt: 100 }
}).sort({ millis: -1 }).limit(10)
Within 24 hours of profiling, we identified 14 distinct query shapes responsible for 87% of all slow operations. The top three offenders were the product search query, the order history aggregation, and the inventory availability check.
Step 2: Explain Plan Analysis — Understanding Why They Are Slow
For each slow query shape, we ran explain("executionStats") to understand exactly how MongoDB was executing the query. The explain output reveals whether the query used an index (IXSCAN) or performed a full collection scan (COLLSCAN), how many documents were examined versus returned, and the execution time breakdown.
// The product search query that was averaging 340ms
db.products.find({
category: "electronics",
brand: "Samsung",
price: { $gte: 100, $lte: 500 },
inStock: true
}).sort({ rating: -1 }).limit(20).explain("executionStats")
// Key findings from explain output:
// stage: COLLSCAN (no matching index!)
// totalDocsExamined: 3,847,291
// totalKeysExamined: 0
// executionTimeMillis: 347
The product search query was scanning nearly 4 million documents to return just 20 results. Despite having 23 indexes on the collection, none matched this specific query shape. This is a textbook example of why reactive indexing fails — you end up with many indexes that serve old query patterns while new, critical queries go unindexed.
Step 3: Index Usage Audit — Eliminating the Waste
We used the $indexStats aggregation to audit every index on the high-traffic collections. This revealed that 14 of the 23 indexes on the products collection had zero or near-zero usage over the past 30 days.
// Audit index usage across the products collection
db.products.aggregate([
{ $indexStats: {} },
{ $project: {
name: 1,
"stats.accesses.ops": 1,
"stats.accesses.since": 1
}},
{ $sort: { "stats.accesses.ops": 1 }}
])
The Optimization Playbook: What We Fixed and How
Fix 1: Compound Indexes Using the ESR Rule
MongoDB's ESR (Equality, Sort, Range) rule is the foundation of effective compound index design. Fields used in equality filters come first, followed by sort fields, then range filters. For the product search query, we designed:
// Optimized compound index following the ESR rule
db.products.createIndex({
category: 1, // Equality
brand: 1, // Equality
inStock: 1, // Equality
rating: -1, // Sort
price: 1 // Range
})
For a complete reference on compound index strategy, see MongoDB's indexing best practices guide.
Fix 2: Aggregation Pipeline Refactoring
The seller analytics dashboard used a complex aggregation pipeline that processed the entire orders collection before filtering. The fix was straightforward but impactful: move $match and $sort stages to the beginning of the pipeline so MongoDB can leverage indexes before processing downstream stages.
// BEFORE: Unoptimized pipeline (8.2s)
db.orders.aggregate([
{ $lookup: { from: "products", ... } },
{ $unwind: "$items" },
{ $match: { sellerId: ObjectId("..."), status: "completed" } },
{ $group: { _id: "$items.category", revenue: { $sum: "$total" } } }
])
// AFTER: Optimized pipeline (1.8s)
db.orders.aggregate([
{ $match: { sellerId: ObjectId("..."), status: "completed" } },
{ $sort: { orderDate: -1 } },
{ $lookup: { from: "products", ... } },
{ $unwind: "$items" },
{ $group: { _id: "$items.category", revenue: { $sum: "$total" } } }
])
Fix 3: Projections and Covered Queries
Several API endpoints were fetching entire product documents (averaging 4KB each) when only the name, price, and thumbnail URL were needed for listing pages. By adding projections and designing indexes that covered the projected fields, we eliminated document fetches entirely.
// Covered query: all returned fields are in the index
db.products.find(
{ category: "electronics", inStock: true },
{ name: 1, price: 1, thumbnailUrl: 1, _id: 0 }
)
// Supporting covering index
db.products.createIndex({
category: 1, inStock: 1, name: 1, price: 1, thumbnailUrl: 1
})
Fix 4: Dropping Unused Indexes
After verifying with $indexStats and correlating with the profiler data, we safely dropped 14 unused indexes across the three primary collections. This freed approximately 2.8GB of RAM and noticeably improved write performance across the platform.
Best Practices for Production
Based on this engagement and hundreds of similar audits, here are the best practices we recommend for maintaining optimal MongoDB query performance at scale.
1. Follow the ESR Rule for Every Compound Index
Equality fields first, then Sort fields, then Range fields. This ordering ensures MongoDB can use the index most efficiently, creating tight bounds on the scan and avoiding in-memory sorts. Review your existing compound indexes against this rule — many teams discover their field order is suboptimal.
2. Profile Continuously, Not Just During Incidents
Enable the slow query profiler permanently at a reasonable threshold (100–200ms). Review profiler output weekly. Query patterns shift as features evolve, and a query that was fast last month may be slow today due to data growth or new access patterns. Mafiree's managed MongoDB monitoring includes automated profiler analysis that flags new slow query shapes within minutes of their first appearance.
3. Audit Index Usage Quarterly
Run $indexStats on every collection with more than 5 indexes. Drop indexes with zero operations over 30 days. Consolidate overlapping indexes into broader compound indexes that serve multiple query shapes. Fewer, smarter indexes always outperform many redundant ones.
4. Put $match First in Every Aggregation Pipeline
This single rule delivers the largest performance improvement in aggregation workloads. A $match stage at the beginning of the pipeline leverages indexes and dramatically reduces the document count flowing into expensive stages like $lookup, $unwind, and $group.
5. Use Projections in Every Query
Never fetch entire documents unless you genuinely need every field. Projections reduce network transfer, memory consumption, and deserialization time. When combined with a covering index, projections allow MongoDB to serve the query entirely from the index without touching the documents at all.
Conclusion: Optimization is a Discipline, Not a One-Time Fix
MongoDB query optimization is not something you do once and forget. Data grows, query patterns shift, and application features evolve. What made our client's optimization successful was not just the initial fixes but the ongoing monitoring framework we established: continuous profiling, automated index audits, and real-time alerting on latency regressions.
- 73% reduction in API latency achieved without adding hardware
- 33% reduction in infrastructure costs from smarter indexing
- Platform now performs better at 12M products than it did at 8M
- Write performance improved as a side-effect of removing unused indexes
If your MongoDB queries are slowing down your application, Mafiree is here to help.
Need help transitioning from one MongoDB cluster to another? Learn how in our guide on seamless data transition with MongoDB Cluster to Cluster Sync.
