MongoDB Indexes Explained
On this page
Symptom: a COLLSCAN reading 200,000 documents
The orders page for a customer gets slower every week. explain shows why. On a test collection of 200,000 orders in MongoDB 9:
db.orders.find({ customerId: 4021, status: "paid" }).explain("executionStats")stage: COLLSCAN totalKeysExamined: 0 totalDocsExamined: 200000 nReturned: 1 executionTimeMillis: 26COLLSCAN means a full collection scan: every document read to return one. The time grows in step with the collection.
Fix: create an index
db.orders.createIndex({ customerId: 1, status: 1 })stage: FETCH <- IXSCAN totalKeysExamined: 1 totalDocsExamined: 1 nReturned: 1 executionTimeMillis: 2IXSCAN means MongoDB walked the index to the matching entries, then FETCHed only those documents. One key examined, one document read.
An index is a separate, sorted B-tree of field values, each pointing to its document. Finding a value takes a few steps down the tree regardless of collection size, and because it's sorted, range queries and sorts on indexed fields are cheap too.
Reading explain output: keys and documents examined
explain("executionStats") is the tool you'll use most (Day 13 goes deeper). Compare three numbers:
nReturned: documents returned,totalKeysExamined: index entries read,totalDocsExamined: documents read.
Ideally all three are close. Many more documents examined than returned means the index is missing or doesn't match the query well.
MongoDB index types
| Type | For |
|---|---|
| Single field | { email: 1 } |
| Compound | { customerId: 1, placedAt: -1 }: several fields, order matters (Day 8) |
| Multikey | Automatically used when an indexed field is an array: one entry per element |
| Unique | { email: 1 }, { unique: true }: rejects duplicates |
| Partial | Index only documents matching a filter: { partialFilterExpression: { status: "open" } } |
| TTL | Delete documents after a time (Day 12) |
| Text, geospatial, wildcard | Search, location, unknown field names (Day 10) |
Every collection has a unique index on _id, which you can't drop.
The write cost of indexes
Each index is updated on every insert, on updates that touch indexed fields, and on deletes. Each takes disk and, ideally, fits in RAM along with your working set. Ten indexes on a write-heavy collection can slow writes noticeably.
So index for real queries:
- Find slow queries with the profiler or slow query log, and in Atlas, the Performance Advisor.
- Check index usage with
$indexStats; drop indexes that nothing uses. - Prefer one well-designed compound index over several single-field ones.
- Build indexes on large production collections at quiet times; builds no longer block reads and writes for the whole duration, but they do add load.
Check yourself
There's no usable index for the query, so MongoDB read all 500,000 documents to return 12. The ratio of documents examined to returned is the key signal. Add an index that matches the query's filter fields.
Create a unique index: db.customers.createIndex({ email: 1 }, { unique: true }). Inserts or updates that would duplicate an email then fail with a duplicate key error. Normalize emails, such as lowercasing, before storing so A@x.com and a@x.com don't both get in.
Every index must be updated on every insert and on updates that touch its fields, so writes get slower, and each index uses disk and memory that competes with your working set. Indexes that no query uses only cost. Check $indexStats and drop unused ones.
An index on an array field. MongoDB creates an index entry for each array element, so queries on any element can use it, but large arrays create many entries.
Run it with .explain("executionStats") and look for an IXSCAN stage, then compare totalKeysExamined and totalDocsExamined with nReturned.