MongoDB Performance Optimization: Why Indexes Are the Difference Between 2ms and 2 Seconds

MongoDB is fast — when it's used correctly. A well-indexed query on a collection with 100 million documents returns in 2ms. The same query without an index takes 4 seconds, scans every document, saturates CPU, and blocks other queries behind it. The difference isn't hardware, configuration, or MongoDB version. It's one index.

A missing index doesn't just slow down one query. It slows down every query behind it in the queue, because that full collection scan is consuming CPU and I/O that other operations need.

What Happens Without an Index

When you query a MongoDB collection without a matching index, MongoDB performs a collection scan (COLLSCAN). It reads every single document in the collection, checks if it matches your query filter, and returns the matches. If the collection has 10 million documents and your query matches 15, MongoDB still reads all 10 million to find those 15.

This is O(n) — linear with the number of documents. When the collection is small (1,000 documents), COLLSCAN is fast enough that you don't notice. When the collection grows to 1 million, 10 million, 100 million documents, COLLSCAN becomes the single biggest performance killer in your system.

The indexed query time barely changes as the collection grows. That's the power of a B-tree index — it's O(log n). Going from 1 million to 100 million documents adds maybe 1ms to an indexed query. Without an index, it adds 20+ seconds.

How MongoDB Indexes Work

A MongoDB index is a B-tree data structure that stores a sorted subset of your document fields along with pointers to the full documents. When you create an index on { status: 1, createdAt: -1 }, MongoDB builds a tree where entries are sorted by status (ascending) then by createdAt (descending). To find all documents with status: 'fraud' created in the last hour, MongoDB walks the tree directly to the right range — it never touches documents that don't match.

The _id field is automatically indexed. Every other query pattern requires you to create indexes explicitly. MongoDB will never create one for you (except _id), and it will happily run COLLSCAN on every query until you tell it not to.

MongoDB doesn't warn you when a query does a COLLSCAN. It just runs slower. You have to actively look for missing indexes — they won't announce themselves.

Types of Indexes and When to Use Each

The ESR Rule: Equality, Sort, Range

The order of fields in a compound index determines its effectiveness. MongoDB uses a principle called ESR — Equality, Sort, Range — to define the optimal field order.

• Equality fields first — Fields in your query that use exact match (status: 'active', type: 'transaction'). These narrow down the index entries to a small subset immediately.
• Sort fields second — Fields in your sort() clause. Placing them after equality fields means MongoDB can read the index in order without a separate sort stage.
• Range fields last — Fields using $gt, $lt, $gte, $lte, $in with multiple values. Range conditions scan a portion of the index; placing them last minimizes the scan range.

Example: db.transactions.find({ accountId: 'A123', amount: { $gt: 1000 } }).sort({ createdAt: -1 }). The optimal compound index is { accountId: 1, createdAt: -1, amount: 1 } — equality (accountId), sort (createdAt), range (amount). Getting this order wrong can mean the difference between an index scan of 50 entries and 50,000 entries.

A compound index with the wrong field order can be worse than no index at all — MongoDB uses it, thinks it's efficient, but still scans thousands of entries. ESR gets the order right.

Covered Queries: The Ultimate Optimization

A covered query is one where MongoDB returns results entirely from the index — it never reads the actual document. This happens when all the fields in your query filter, projection, and sort are part of the index.

Example: You have an index on { status: 1, amount: 1, merchantId: 1 }. If your query is db.transactions.find({ status: 'flagged' }, { projection: { amount: 1, merchantId: 1, _id: 0 } }), MongoDB returns the results directly from the index without touching the collection at all. This is the fastest possible query — no document fetch, no deserialization, pure index traversal.

Use explain('executionStats') to verify: look for totalDocsExamined: 0 in the output. If it's zero, the query is covered.

Finding Missing Indexes

MongoDB won't tell you that you're missing an index — but several tools will.

explain() — The First Tool

Run any query with .explain('executionStats') appended. Look at three fields: winningPlan.stage (COLLSCAN = no index, IXSCAN = index used), totalDocsExamined vs totalKeysExamined (if docsExamined >> keysExamined, you're reading documents unnecessarily), and executionTimeMillis (the actual time).

• COLLSCAN in winningPlan — No index. Create one.
• IXSCAN but totalDocsExamined much higher than nReturned — Index is used but not selective enough. Refine the compound index.
• SORT_KEY_GENERATOR in the plan — MongoDB is sorting in memory instead of using the index order. Add the sort field to the index.
• executionTimeMillis > 100ms for a targeted query — Something is wrong. Investigate the plan.

Atlas Performance Advisor

If you're on MongoDB Atlas (M10+), the Performance Advisor analyzes your actual query patterns and suggests indexes automatically. It shows which queries are slow, what index would fix them, and the estimated improvement. It also flags redundant indexes that waste storage and slow writes. This is the easiest way to find missing indexes — it does the analysis for you.

The Profiler

MongoDB's database profiler logs slow queries. Set the profiling level to 1 (slow operations only) with a threshold: db.setProfilingLevel(1, { slowms: 50 }). This logs every query that takes longer than 50ms to the system.profile collection. Review it periodically to catch queries that have degraded as data grows.

Index Costs: Why You Can't Index Everything

Indexes speed up reads but slow down writes. Every insert, update, and delete must also update every index on the collection. A collection with 10 indexes means every write triggers 10 index updates. For write-heavy workloads (event logging, telemetry, real-time transaction recording), excess indexes are a direct write-throughput bottleneck.

Indexes also consume RAM. MongoDB keeps frequently accessed index pages in the WiredTiger cache. If your indexes exceed the available cache, MongoDB reads index pages from disk — and an index read from disk is 100x slower than from memory. Run db.collection.stats() and check totalIndexSize. If it exceeds 60% of your available RAM, you either need more memory or fewer indexes.

The goal isn't maximum indexes. It's minimum indexes that cover maximum query patterns. Every unnecessary index costs write performance and RAM.

Partial Indexes: Index Less, Query Faster

A partial index only includes documents that match a filter expression. If 90% of your queries target documents with status: 'active', a partial index on { createdAt: -1 } with partialFilterExpression: { status: 'active' } is 90% smaller than a full index on the same field. Smaller index = fits in RAM = faster reads = lower write overhead.

This is especially powerful for archival patterns. If your collection has 100 million documents but only 2 million are 'active', the partial index covers those 2 million. Queries on active documents use the small, fast index. Queries on archived documents (rare) fall back to a collection scan or a separate index — which is fine because they're not latency-sensitive.

The MongoDB Index Optimization Checklist

• 1. Run explain('executionStats') on your top 20 queries — find every COLLSCAN and fix it.
• 2. Follow the ESR rule for compound indexes — Equality, Sort, Range.
• 3. Design covered queries where possible — include projected fields in the index.
• 4. Use Atlas Performance Advisor — it finds missing indexes and flags redundant ones automatically.
• 5. Set up the profiler at slowms: 50 — catch slow queries before they compound.
• 6. Monitor totalIndexSize vs available RAM — indexes must fit in memory.
• 7. Use partial indexes for collections with clear hot/cold data patterns.
• 8. Remove unused indexes — db.collection.aggregate([{ $indexStats: {} }]) shows which indexes haven't been used.
• 9. Limit to 5-6 indexes per collection — more than that requires strong justification.
• 10. Re-evaluate indexes quarterly — query patterns change as features ship. Indexes that were critical 6 months ago might be unused today.

The most common MongoDB performance problem isn't a misconfigured cluster or an undersized instance. It's a missing index on a query that runs 10,000 times per minute. find() + explain() takes 30 seconds. The fix saves hours of cumulative latency every day.