Query Optimization

A query that takes 2ms in development can take 2000ms in production when your collection grows from 100 documents to 100,000. Query optimization is the process of finding those slow queries before your users do — and fixing them.

MongoDB gives you two main tools for this:

• The profiler — automatically records slow queries so you can find them
• explain() — shows you exactly how a specific query runs

We covered explain() in the Indexes section. This file focuses on the profiler and the practical workflow for finding and fixing slow queries.

What Makes a Query Slow

Before looking for slow queries, it helps to know what causes them:

Collection scan (COLLSCAN) — no index exists for the query filter. MongoDB reads every document. This is the most common cause of slow queries.

In-memory sort — no index supports the sort. MongoDB loads all matching documents into memory and sorts them. Slow for large result sets.

Large result sets with no limit — fetching thousands of documents when you only need 10.

Unselective index — the index exists but matches too many documents. For example, an index on enrolled where 95% of students are enrolled — MongoDB still has to fetch thousands of documents.

$lookup without an index — joining two collections where the foreign field has no index. MongoDB scans the entire foreign collection for every document in the pipeline.

Large skip values — skip(10000) makes MongoDB count through 10,000 documents before returning results.

The MongoDB Profiler

The profiler is a built-in MongoDB tool that records operations that take longer than a threshold you set. It writes these slow operations to a special collection called system.profile in your database.

Profiler Levels

The profiler has three levels:

Level 2 records everything and is useful for debugging but creates a lot of overhead. Use level 1 in production — it only records the queries you actually need to fix.

Turn the Profiler On

// Level 1 — record queries slower than 100ms
db.setProfilingLevel(1, { slowms: 100 })

// Level 1 — record queries slower than 50ms
db.setProfilingLevel(1, { slowms: 50 })

// Level 2 — record everything
db.setProfilingLevel(2)

// Turn profiler off
db.setProfilingLevel(0)

Check Current Profiler Status

db.getProfilingStatus()

Output:

{
  was: 1,
  slowms: 100,
  sampleRate: 1
}

Reading the Profiler Output

The profiler writes to system.profile. Query it like any other collection:

// See the most recent slow queries
db.system.profile.find().sort({ ts: -1 }).limit(10)

A typical profiler entry looks like this:

{
  op: "query",
  ns: "school.students",
  command: {
    find: "students",
    filter: { name: "Ali Hassan" },
    sort: { age: -1 }
  },
  keysExamined: 0,
  docsExamined: 5000,
  nreturned: 1,
  millis: 145,
  planSummary: "COLLSCAN",
  ts: ISODate("2024-09-01T10:23:45.123Z")
}

The key fields to read:

Red flags:

• planSummary: "COLLSCAN" — no index used
• docsExamined much larger than nreturned — scanning too many documents
• millis over 100 — slow query

Find the Slowest Queries

// Top 5 slowest queries
db.system.profile
  .find({ op: "query" })
  .sort({ millis: -1 })
  .limit(5)
  .project({ command: 1, millis: 1, planSummary: 1, docsExamined: 1, nreturned: 1 })

Find All Collection Scans

// All queries doing a collection scan
db.system.profile.find({
  planSummary: "COLLSCAN"
}).sort({ millis: -1 })

Find Slow Queries on the Students Collection

db.system.profile.find({
  ns: "school.students",
  millis: { $gt: 100 }
}).sort({ millis: -1 })

The Optimization Workflow

Here is the step-by-step process for finding and fixing slow queries:

Step 1 — Enable the profiler

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

Set the threshold to something reasonable. Start with 100ms — any query slower than that is worth investigating.

Step 2 — Run your application normally

Let your app run for a while — use it, test it, load some data. The profiler collects slow queries in the background.

Step 3 — Check the profiler

// What are the slowest queries?
db.system.profile
  .find({ millis: { $gt: 50 } })
  .sort({ millis: -1 })
  .limit(20)
  .project({ command: 1, millis: 1, planSummary: 1, docsExamined: 1 })

Step 4 — Run explain() on the slow query

Take the slow query from the profiler and run explain("executionStats") on it:

db.students
  .find({ name: "Ali Hassan" })
  .sort({ age: -1 })
  .explain("executionStats")

Look for:

• stage: "COLLSCAN" — needs an index
• stage: "SORT" — sort needs an index
• totalDocsExamined much larger than nReturned

Step 5 — Add the right index

// Query filters on name, sorts on age — create compound index
db.students.createIndex({ name: 1, age: -1 })

Step 6 — Verify with explain() again

db.students
  .find({ name: "Ali Hassan" })
  .sort({ age: -1 })
  .explain("executionStats")

Confirm:

• stage: "IXSCAN" — index is being used
• totalDocsExamined equals nReturned
• executionTimeMillis is much lower

Using hint() During Optimization

When you are testing different indexes, use hint() to force MongoDB to use a specific one:

// Test with index on name only
db.students
  .find({ grade: "10th", enrolled: true })
  .sort({ name: 1 })
  .hint({ name: 1 })
  .explain("executionStats")

// Test with compound index
db.students
  .find({ grade: "10th", enrolled: true })
  .sort({ name: 1 })
  .hint({ grade: 1, enrolled: 1, name: 1 })
  .explain("executionStats")

Compare executionTimeMillis and totalDocsExamined between the two. Use whichever performs better.

Common Optimization Mistakes

Mistake 1 — Indexing after the fact

Many developers only think about indexes after their app is already slow in production. By then, adding an index to a collection with millions of documents takes a long time and blocks the system.

Fix: Think about query patterns during schema design. Add indexes before you have performance problems.

Mistake 2 — Too many indexes

Every index slows down writes because MongoDB must update every index on every write. A collection with 20 indexes has very fast reads but very slow inserts and updates.

Fix: Only create indexes for queries you actually run. Drop indexes that are not being used.

Mistake 3 — Wrong field order in compound indexes

A compound index on { enrolled: 1, grade: 1 } does not help a query that only filters on grade. The leading field must match.

Fix: Always put the most selective and most commonly queried field first. We cover compound index field order in detail in the next file.

Mistake 4 — Ignoring the profiler

Most developers never enable the profiler. They only investigate slow queries when users complain.

Fix: Enable the profiler in all environments. Check it regularly during development, not just when something breaks.

Mistake 5 — Large skip values

// Slow — MongoDB counts through 50,000 documents
db.students.find().skip(50000).limit(10)

For deep pagination, $skip gets slower and slower as the offset grows.

Fix: Use range-based pagination instead — store the last _id and use it in the next query:

// Fast — uses _id index directly
db.students
  .find({ _id: { $gt: lastSeenId } })
  .limit(10)
  .sort({ _id: 1 })

Profiler Size Limit

The system.profile collection has a default size of 1MB. When it fills up, older entries are automatically removed. On busy systems, slow queries from earlier may get pushed out before you see them.

Increase the size if needed by recreating the collection with a larger cap:

// Turn profiler off first
db.setProfilingLevel(0)

// Drop and recreate with larger size — 10MB
db.system.profile.drop()
db.createCollection("system.profile", { capped: true, size: 10485760 })

// Turn profiler back on
db.setProfilingLevel(1, { slowms: 100 })

School System — Finding Slow Queries

Let's enable the profiler and find slow queries in our school database:

// Enable profiler — record queries over 50ms
db.setProfilingLevel(1, { slowms: 50 })

// Run some queries that might be slow
db.students.find({ name: "Ali Hassan" }).sort({ age: -1 }).toArray()
db.students.find({ "address.city": "Lahore", enrolled: true }).toArray()
db.students.aggregate([
  { $match: { enrolled: true } },
  { $group: { _id: "$grade", count: { $sum: 1 } } }
])

// Check what the profiler caught
db.system.profile
  .find({ millis: { $gt: 50 } })
  .sort({ millis: -1 })
  .project({ command: 1, millis: 1, planSummary: 1, docsExamined: 1, nreturned: 1 })

// Find all collection scans
db.system.profile.find({
  ns: "school.students",
  planSummary: "COLLSCAN"
})

// Take a slow query and run explain on it
db.students
  .find({ "address.city": "Lahore", enrolled: true })
  .explain("executionStats")

// Add the missing index
db.students.createIndex({ "address.city": 1, enrolled: 1 })

// Verify improvement
db.students
  .find({ "address.city": "Lahore", enrolled: true })
  .explain("executionStats")

Quick Reference

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

// Check profiler status
db.getProfilingStatus()

// Find slowest queries
db.system.profile.find().sort({ millis: -1 }).limit(10)

// Find collection scans
db.system.profile.find({ planSummary: "COLLSCAN" })

// Find slow queries on specific collection
db.system.profile.find({ ns: "school.students", millis: { $gt: 100 } })

// Disable profiler
db.setProfilingLevel(0)