← Back to blog

MongoDB Slow Queries: How to Find and Fix Them

DBA Copilot Team · · 9 min read
mongodb performance slow-queries indexes tuning

MongoDB Slow Queries: How to Find and Fix Them

Slow queries are the most common MongoDB performance problem — and also the most fixable. A query that takes 10 seconds without an index can take under 1 millisecond with the right one. This guide shows you how to find slow queries, understand why they're slow, and fix them.

The Two Tools You Need

MongoDB gives you two complementary tools for finding slow queries:

The Profiler captures queries that exceeded a time threshold — your historical record of what was slow.

explain("executionStats") shows you exactly how MongoDB executed a specific query — your diagnostic tool for understanding why it's slow.

Used together, they let you find slow queries and understand their root cause.

Step 1: Enable the Profiler

The profiler records operations that exceed a configurable threshold. Check if it's already enabled:

db.getProfilingStatus()
// { "was": 0, "slowms": 100, "sampleRate": 1 }

was: 0 means profiling is off. Enable it for queries over 100ms:

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

Profiling levels: - Level 0 — Off (default) - Level 1 — Log queries slower than slowms - Level 2 — Log ALL queries (high overhead, avoid in production)

For production, Level 1 with slowms: 100 is a safe default. You can also enable it globally in mongod.conf:

operationProfiling:
  mode: slowOp
  slowOpThresholdMs: 100

Step 2: Query the Profiler

The profiler stores records in system.profile, a capped collection in each database.

Find the slowest recent queries

db.system.profile.find().sort({ millis: -1 }).limit(20).pretty()

Find queries by collection

db.system.profile.find({ ns: "mydb.orders" }).sort({ millis: -1 }).limit(10)

Find full collection scans (COLLSCAN)

db.system.profile.find({
  planSummary: /COLLSCAN/
}).sort({ millis: -1 }).limit(20)

This is your most important query. Every COLLSCAN is a query scanning the entire collection without an index — almost always fixable.

Find inefficient queries (examining far more documents than returned)

db.system.profile.find({
  docsExamined: { $exists: true },
  $expr: {
    $gt: [
      { $divide: ["$docsExamined", { $add: ["$nreturned", 1] }] },
      100
    ]
  }
}).sort({ millis: -1 }).limit(20).projection({
  ns: 1, millis: 1, docsExamined: 1, nreturned: 1,
  planSummary: 1, query: 1, command: 1
})

A ratio of docsExamined / nreturned above 10 is a red flag. It means MongoDB read 10+ documents for every 1 it returned — wasted work.

Find the most frequently slow queries (aggregated)

db.system.profile.aggregate([
  { $match: { op: { $in: ["query", "command"] } } },
  {
    $group: {
      _id: "$planSummary",
      count: { $sum: 1 },
      avgMillis: { $avg: "$millis" },
      maxMillis: { $max: "$millis" },
      totalMillis: { $sum: "$millis" }
    }
  },
  { $sort: { totalMillis: -1 } }
])

A complete profiler analysis query

db.system.profile.aggregate([
  { $match: { op: { $in: ["query", "update", "command"] }, millis: { $gt: 0 } } },
  {
    $group: {
      _id: "$ns",
      count: { $sum: 1 },
      avgMillis: { $avg: "$millis" },
      maxMillis: { $max: "$millis" },
      collscans: { $sum: { $cond: [{ $regexMatch: { input: { $ifNull: ["$planSummary", ""] }, regex: "COLLSCAN" } }, 1, 0] } }
    }
  },
  { $sort: { maxMillis: -1 } },
  { $limit: 10 }
])

This groups by collection and shows the count, average time, max time, and number of COLLSCANs per collection.

Step 3: Understand explain() Output

Once you've identified a slow query from the profiler, run it with .explain("executionStats") to understand exactly how MongoDB executes it.

db.orders.find(
  { status: "pending", amount: { $gt: 1000 } }
).explain("executionStats")

The key fields to look at

winningPlan.stage — the execution strategy: - COLLSCAN — full collection scan. Bad. Add an index. - IXSCAN — index scan. Good. - FETCH — retrieving documents by index key. Normal after IXSCAN. - SORT — in-memory sort. Can be slow without a sort index. - SORT_MERGE — merging sorted results. Usually from compound indexes.

executionStats.nReturned — documents returned to the client.

executionStats.totalDocsExamined — documents MongoDB read.

executionStats.totalKeysExamined — index keys scanned.

executionStats.executionTimeMillis — total query time.

The golden ratio

totalDocsExamined / nReturned

In a perfect query, this is 1.0 — MongoDB reads exactly the documents it returns. In practice, ratios under 10 are acceptable. Ratios above 100 indicate a serious indexing problem.

Example: COLLSCAN (bad)

{
  "winningPlan": {
    "stage": "COLLSCAN",
    "filter": { "status": { "$eq": "pending" }, "amount": { "$gt": 1000 } }
  },
  "executionStats": {
    "nReturned": 15,
    "executionTimeMillis": 8432,
    "totalDocsExamined": 4500000,
    "totalKeysExamined": 0
  }
}

MongoDB scanned 4.5 million documents to return 15. Time: 8.4 seconds. This needs an index.

Example: IXSCAN (good)

{
  "winningPlan": {
    "stage": "FETCH",
    "inputStage": {
      "stage": "IXSCAN",
      "keyPattern": { "status": 1, "amount": 1 },
      "indexName": "status_1_amount_1"
    }
  },
  "executionStats": {
    "nReturned": 15,
    "executionTimeMillis": 2,
    "totalDocsExamined": 15,
    "totalKeysExamined": 18
  }
}

Same query with an index: 15 documents examined, 15 returned, 2 milliseconds. A 4,000x improvement.

Step 4: Create the Right Index

Once you know which query is slow and why, creating the right index is usually straightforward.

Single field index

For queries filtering on one field:

// Query: db.orders.find({ status: "pending" })
db.orders.createIndex({ status: 1 })

Compound index (multiple fields)

For queries filtering on multiple fields, use a compound index. The field order matters:

// Query: db.orders.find({ status: "pending", customer_id: "C123" })
db.orders.createIndex({ status: 1, customer_id: 1 })

The ESR Rule for compound indexes

When building compound indexes, follow the ESR rule: 1. Equality fields first (exact matches: status: "pending") 2. Sort fields second (fields in .sort()) 3. Range fields last (inequality operators: $gt, $lt, $in)

// Query: db.orders.find({ status: "pending", amount: { $gt: 1000 } }).sort({ created_at: -1 })
// ESR: status (equality), created_at (sort), amount (range)
db.orders.createIndex({ status: 1, created_at: -1, amount: 1 })

Index for sort operations

Without an index matching the sort, MongoDB sorts in memory. For large result sets, this is slow and memory-intensive.

// Query: db.events.find({ user_id: "U123" }).sort({ timestamp: -1 })
db.events.createIndex({ user_id: 1, timestamp: -1 })

The -1 means descending order. Match the sort direction in your index to avoid an in-memory sort step.

Partial index (index a subset of documents)

If only a subset of documents is queried, a partial index is smaller and faster:

// Only index pending orders (not completed ones, which are rarely queried)
db.orders.createIndex(
  { customer_id: 1, created_at: -1 },
  { partialFilterExpression: { status: "pending" } }
)

Text index (for text search)

db.products.createIndex({ name: "text", description: "text" })
// Query:
db.products.find({ $text: { $search: "wireless keyboard" } })

Step 5: Verify the Fix

After creating an index, verify MongoDB uses it:

db.orders.find(
  { status: "pending", amount: { $gt: 1000 } }
).explain("executionStats")

Check that: - winningPlan.stage is no longer COLLSCAN - totalDocsExamined is close to nReturned - executionTimeMillis has dropped significantly

Force MongoDB to use a specific index (for testing)

db.orders.find({ status: "pending" }).hint({ status: 1, amount: 1 }).explain()

Common Indexing Mistakes

Over-indexing

Every index slows down writes (inserts, updates, deletes must update all indexes). Review unused indexes regularly:

db.orders.aggregate([{ $indexStats: {} }])

Drop indexes with zero accesses:

db.orders.dropIndex("old_unused_index_name")

Indexing low-cardinality fields alone

An index on a boolean field ({ active: 1 }) where 95% of documents have active: true is nearly useless — MongoDB may choose a COLLSCAN instead because the index doesn't filter enough. Combine it with a more selective field:

// Better: combine with a more selective field
db.users.createIndex({ active: 1, last_login: -1 })

Wrong field order in compound indexes

A compound index on { status: 1, created_at: -1 } supports: - Queries filtering on status alone - Queries filtering on status + created_at

It does NOT efficiently support: - Queries filtering on created_at alone (requires a separate index)

Not indexing sort fields

// This query will do an in-memory sort even with an index on user_id
db.events.find({ user_id: "U123" }).sort({ timestamp: -1 })

// Fix: include timestamp in the index
db.events.createIndex({ user_id: 1, timestamp: -1 })

Diagnosing Aggregation Pipeline Performance

Aggregation pipelines have their own performance considerations. Always run with .explain("executionStats"):

db.orders.explain("executionStats").aggregate([
  { $match: { status: "pending" } },
  { $group: { _id: "$customer_id", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
])

Key principles for fast pipelines:

  1. Put $match first — filter early to reduce documents flowing through the pipeline
  2. Put $sort before $group if possible — can use an index
  3. Use $project to reduce document size early in the pipeline
  4. $lookup (joins) are expensive — index the foreign key field
// Good: $match first, reduces data early
[
  { $match: { status: "pending", created_at: { $gt: ISODate("2024-01-01") } } },
  { $group: { _id: "$customer_id", count: { $sum: 1 } } }
]

// Bad: $group first, processes all documents before filtering
[
  { $group: { _id: "$customer_id", count: { $sum: 1 } } },
  { $match: { count: { $gt: 10 } } }
]

Profiler Management

The profiler's system.profile collection is capped — it automatically removes old entries when it fills up. Check and resize it:

// Check current size
db.system.profile.stats().maxSize

// Resize (must drop and recreate)
db.setProfilingLevel(0)
db.system.profile.drop()
db.createCollection("system.profile", { capped: true, size: 10485760 }) // 10MB
db.setProfilingLevel(1, { slowms: 100 })

Exporting Profiler Data for Analysis

Export profiler data to a file for offline analysis or sharing with your team:

mongoexport \
  --db mydb \
  --collection system.profile \
  --sort '{"ts":-1}' \
  --limit 1000 \
  --out profiler_$(date +%Y%m%d_%H%M%S).json

Frequently Asked Questions

How do I find slow queries if the profiler wasn't enabled during the incident? Check the MongoDB log file (mongod.log). Operations exceeding slowOpThresholdMs (default 100ms) are always logged, even without the profiler enabled. Search for "Slow query" in the log.

Should I index every field I query on? No. Index fields that are frequently queried and highly selective (many distinct values). Low-selectivity fields (booleans, status fields with few values) are often better as secondary fields in compound indexes. Aim for indexes that filter to less than 10% of the collection.

My query is fast individually but the application is still slow. Why? Could be query frequency — a 10ms query running 1,000 times per second consumes significant resources. Check mongostat for overall operation rates. Also check for N+1 query patterns in your application code.

Can I create an index without blocking reads and writes? Yes. Use background index builds (available in MongoDB 4.2+, the default from 4.4):

// In MongoDB 4.4+, all index builds are non-blocking by default
db.orders.createIndex({ status: 1, created_at: -1 })

In MongoDB 4.2 and earlier, add { background: true }.

How many indexes is too many? There's no fixed limit, but each index adds write overhead and consumes RAM. A collection with more than 5-6 indexes should be reviewed. Use $indexStats to identify unused indexes and drop them.

Analyse Your MongoDB Queries Automatically

Running through profiler output, explain plans, and index statistics for multiple collections takes time. Knowing which index to create for a complex query requires experience with MongoDB's query planner.

DBA Copilot automates this analysis. Upload your profiler export, explain() output, or serverStatus.json and the AI identifies the slow query patterns, diagnoses the root cause, and recommends the exact index to create — with the correct field order following the ESR rule.

Try DBA Copilot free — no credit card required

Related: How to Diagnose MongoDB Performance Issues

Want to analyze your database performance automatically?

Try DBA Copilot free — upload your AWR/ASH report and get an AI-powered diagnosis in seconds.

Start for free