How Mafiree Slashed MongoDB Query Latency by 73%

Background

Mafiree's MongoDB consulting team took on a performance overhaul for a high-traffic Indian e-commerce platform, cutting average API response times from 340ms down to 92ms — a 73% improvement. This was achieved purely through smarter query design, better indexing, and pipeline restructuring, with no new hardware added.

Why MongoDB Queries Slow Down

MongoDB query optimization  problems typically surface when data volume outpaces the original design assumptions. The four main culprits are:

• Collection Scans (COLLSCAN): Without a matching index, MongoDB reads every document in a collection — a process that can stretch into seconds on large datasets.
• Wrong Indexes: Indexes designed for one query pattern won't help a differently structured query, making reactive index creation a flawed strategy.
• Unbounded Aggregation Pipelines: Running heavy stages like $lookup and $unwind before a $match filter forces MongoDB to process the entire collection unnecessarily.
• Over-Fetching: Pulling full documents when only a handful of fields are needed burns memory, bandwidth, and CPU unnecessarily.

The Client's Problem

The client ran an e-commerce marketplace on MongoDB 6.0 — a 3-node AWS replica set handling 2 million daily active users, 12 million product documents, and over 80 million orders. Over 18 months of growth, performance had quietly deteriorated: product search averaged 340ms, the checkout flow timed out during flash sales, and the seller analytics dashboard took over 8 seconds to load. Indexes had been added reactively over time, leaving 23 indexes on the products collection — many redundant or completely unused.

The 3-Step Diagnostic Process

Step 1 — Profiler Analysis: MongoDB's built-in profiler was enabled at level 1 with a 100ms threshold. Within 24 hours, 14 query shapes were identified as responsible for 87% of all slow operations, with product search, order history aggregation, and inventory availability checks topping the list.

Step 2 — Explain Plan Analysis: Running explain("executionStats") on each slow query revealed exactly what MongoDB was doing under the hood. The product search query was scanning nearly 4 million documents just to return 20 results — and despite having 23 indexes on the collection, not a single one matched that query's shape.

Step 3 — Index Usage Audit: Using $indexStats, every index was assessed for actual usage. Fourteen of the 23 indexes had seen zero or near-zero activity in the past 30 days. Critically, unused indexes are not neutral — they slow down every write operation on the collection.

The Four Fixes

Fix 1 — ESR-Based Compound Indexes: The ESR (Equality, Sort, Range) rule dictates that compound index fields should be ordered by equality filters first, then sort fields, then range filters. Applying this to the product search query reduced documents examined from 3.8 million to just 847, dropping latency from 340ms to 92ms.

Fix 2 — Aggregation Pipeline Reordering: The seller dashboard pipeline was running $lookup and $unwind across the full 80-million-document orders collection before any filtering occurred. Simply moving $match and $sort to the front of the pipeline reduced the working set to around 45,000 documents before the expensive stages ran, cutting dashboard load time from 8.2s to 1.8s.

Fix 3 — Projections and Covered Queries: API endpoints were retrieving entire 4KB product documents when only a few fields were needed. Adding projections and designing covering indexes eliminated full document fetches, reducing listing page latency by 60% and network bandwidth consumption by 45%.

Fix 4 — Dropping Unused Indexes: After safely confirming which indexes could be removed, 14 were dropped. This freed 2.8GB of RAM, improved write latency by 18%, and cut monthly AWS spend by $1,600.

Results & Best Practices

The final numbers: product search at 92ms, checkout p99 down to 280ms, seller dashboard at 1.8s, collection scans reduced from 4,200 to 12 per hour, indexes trimmed from 23 to 9, and AWS costs down from $4,800 to $3,200 monthly.

The blog wraps up with five standing best practices: apply the ESR rule to all compound indexes, run the slow query profiler continuously, audit index usage quarterly, always lead aggregation pipelines with $match, and use projections in every query. Performance optimization isn't a one-time task — sustained gains require ongoing monitoring, regular index audits, and alerting on latency regressions as the platform continues to scale.