MongoDB slow query optimization: lessons from production

MongoDB slow query optimization: lessons from production

Slow queries in MongoDB don't announce themselves—they silently degrade user experience until someone checks the logs. We've all been there: a feature works fine in development, ships to production, and suddenly requests timeout when real data volumes hit. MongoDB slow query optimization is the difference between a platform that scales smoothly and one that grinds to a halt under load.

The good news is that MongoDB gives us concrete tools to find and fix slow queries before they hurt customers. Over the past decade working on payment systems, ride-hailing platforms, and high-throughput SaaS applications, we've learned that most slow queries follow predictable patterns—and most can be fixed with the same systematic approach. This guide walks through that approach so your team can catch performance regressions early.

Enable the profiler for operations above a threshold

The MongoDB profiler is like having a flight recorder for your database. It captures detailed metrics on every operation that exceeds a threshold you define, letting you spot patterns rather than chase one-off slow queries. In production, we never profile everything—that overhead adds latency and fills disk quickly. Instead, we set a threshold that catches the genuinely slow operations.

Start by checking the current profiler state:

db.getProfilingLevel()
db.getProfilingStatus()

Enable the profiler at level 1 (log slow operations) with a 100ms threshold:

db.setProfilingLevel(1, { slowms: 100 })

After running your application for a few minutes or hours, query the system.profile collection to see what got logged:

db.system.profile.find({
  millis: { $gt: 100 }
}).sort({ ts: -1 }).limit(10).pretty()

Look for patterns: which queries keep showing up? Which ones consistently exceed your threshold? In one e-commerce system we worked on, the profiler revealed that order status updates were taking 400–500ms during peak traffic—not because the update was complex, but because the collection scan was hitting thousands of documents. That single discovery drove the entire optimization effort that day.

Adjust the slowms threshold based on your SLAs. For APIs that serve user requests, 50ms might be your target; for batch jobs, 1000ms might be acceptable. The profiler is most useful when tuned to catch operations that matter for your use case.

Use explain() to check COLLSCAN vs IXSCAN and documents examined

The explain() method is your diagnostic tool. It reveals how MongoDB executes a query, not just that it's slow. When you run explain("executionStats"), MongoDB shows you the execution plan, the number of documents examined, the number of documents returned, and the stage breakdown.

Let's look at a real scenario. Suppose we have an orders collection with millions of documents, and we're querying for paid orders:

db.orders.find({ status: "paid" }).explain("executionStats")

This returns output like:

{
  "executionStats": {
    "executionStages": {
      "stage": "COLLSCAN",
      "nReturned": 45000,
      "totalDocsExamined": 2500000,
      "executionTimeMillis": 1200
    }
  }
}

That COLLSCAN (collection scan) is the red flag. MongoDB examined 2.5 million documents to return 45,000. It had to touch every single document in the collection. For comparison, a good query might look like:

{
  "executionStats": {
    "executionStages": {
      "stage": "IXSCAN",
      "nReturned": 45000,
      "totalDocsExamined": 45000,
      "executionTimeMillis": 15
    }
  }
}

Here, IXSCAN (index scan) examined exactly as many documents as it returned. That's the ideal ratio. It used an index to locate matching documents efficiently, then fetched only those documents from disk. The query runs in 15ms instead of 1200ms.

The explain() output also shows you which fields were used in the query and whether any sort stage had to spill to disk (a sign that index coverage could be improved). Spend time reading explain output; it trains your intuition for what fast looks like.

Check the ratio: documents examined should be close to documents returned

The ratio of totalDocsExamined to nReturned tells you how efficiently the query runs. A ratio of 1.0 is perfect—MongoDB examined one document for every document it returned. A ratio of 10.0 or higher indicates waste: MongoDB looked at 10 documents for every one it actually needed.

Here's how to calculate and track this for a batch of slow queries:

db.system.profile.find({
  millis: { $gt: 200 }
}).forEach(doc => {
  const stats = doc.execStats;
  if (stats) {
    const ratio = stats.totalDocsExamined / stats.nReturned;
    print(
      `Query: ${JSON.stringify(doc.command.filter || {})} | ` +
      `Ratio: ${ratio.toFixed(2)} | ` +
      `Time: ${doc.millis}ms`
    );
  }
});

In a travel booking system, we found a query that examined 150,000 documents to return 200 results—a ratio of 750. The query was filtering trips by destination and date range, but only destination had an index. Adding a compound index on [destination, date] brought the ratio down to 1.2 and reduced execution time from 800ms to 12ms. That's a 65x improvement from one index addition.

When the ratio is high, it usually means:

• The query lacks an index on the filter fields.
• An index exists but is ordered wrong; MongoDB can't use it effectively.
• The query scans an index but then filters many documents in-memory.
• A sort stage is forcing a full dataset load before limiting results.

A ratio between 1.0 and 3.0 is usually acceptable; anything above 5.0 is worth investigating.

Fix: add or reorder indexes, add projections, reduce result size

Once you've identified a slow query, you have several levers to pull. Let's go through each.

Add or reorder indexes

Indexes are MongoDB's primary tool for reducing documents examined. If the profiler shows a COLLSCAN, the first question is: does an appropriate index exist?

Check existing indexes:

db.orders.getIndexes()

If you need to index on status, create it:

db.orders.createIndex({ status: 1 })

For compound queries, index order matters. If you filter by status and then by created_at, create a compound index:

db.orders.createIndex({ status: 1, created_at: -1 })

MongoDB can use this index to satisfy both conditions. Order the index fields by: equality fields first (status), then sort fields (created_at). This is called the ESR rule: Equality, Sort, Range.

Apply ESR to a query that filters users by region, sorts by signup date, and has a date range:

// Query
db.users.find({
  region: "US",          // equality
  signup_at: { $gte: startDate, $lt: endDate }  // range
}).sort({ signup_at: -1 })

// Index (ESR order)
db.users.createIndex({
  region: 1,             // equality
  signup_at: -1          // sort & range
})

Add projections to reduce data transfer

Even with a good index, if you retrieve every field and MongoDB only needs a few, you're wasting bandwidth and memory. Use projections to fetch only what you need:

db.orders.find(
  { status: "paid" },
  { _id: 1, order_id: 1, total: 1 }  // projection: only these fields
).explain("executionStats")

A projection that matches your index fields is called a covered query—MongoDB serves the result entirely from the index without touching the main collection. Covered queries are the fastest because they avoid a second lookup.

Limit result size

If you're returning 100,000 documents when you only need the first 100, limit your result set:

db.orders.find({ status: "paid" }).limit(100).explain("executionStats")

Limit is particularly effective when combined with sort and index. MongoDB can stop scanning the index once it has enough matching documents to satisfy the limit, rather than examining the entire result set.

Common mistakes

We see these patterns repeatedly in production systems:

• Ignoring the ratio. Teams add an index and assume the query is fixed without checking explain(). A ratio of 50.0 is still a problem, even if there's an index. Keep optimizing.
• Indexing too much. Every index you add increases write latency and storage. Index only the fields your queries actually filter or sort on. We've seen collections with 40 indexes where 8 were enough.
• Creating indexes without testing. Test index creation on production data volume before deploying. A query might be fast with 1000 documents but slow with 100 million. Build a replica of your data locally and profile against it.
• Not monitoring after optimization. A query is fast today and slow tomorrow if data distribution changes. Monitor the profiler continuously, not just during optimization sprints.
• Forgetting about schema design. Sometimes a slow query signals a deeper schema problem. If you're frequently joining across collections with lookup, consider denormalization.

Key takeaways

• Enable the MongoDB profiler at a threshold that matches your SLAs; aim for slowms between 50–200ms for user-facing APIs.
• Use explain("executionStats") to identify COLLSCAN operations and calculate the ratio of documents examined to documents returned.
• Target a ratio below 3.0; anything higher indicates the query can be optimized with indexes, projections, or limits.
• Follow the ESR rule when building compound indexes: Equality fields first, then Sort fields, then Range fields.
• Covered queries (where the index contains all data needed) are the fastest; design indexes to support them when possible.
• Treat index additions as changes to monitor and roll back, just like code changes; measure impact with explain() before and after.