Index Strategies
Creating indexes is easy — creating the right indexes is an art. Poor index choices waste memory, slow down writes, and may not even speed up the reads they were intended for. This guide covers the core principles for effective index design in MongoDB.
The ESR Rule (Equality, Sort, Range)
When building compound indexes, order fields according to ESR: Equality fields first, then Sort fields, then Range fields. This ordering maximizes how much of the index MongoDB can use before having to resort to in-memory filtering.
ESR rule example
// Query: find orders for a specific user, sorted newest-first,
// created within the last 30 days, with a specific status
db.orders.find({
userId: "user_abc", // Equality
status: "shipped", // Equality
createdAt: { $gte: thirtyDaysAgo } // Range
}).sort({ createdAt: -1 }) // Sort
// WRONG index order (range before sort):
db.orders.createIndex({ userId: 1, createdAt: -1, status: 1 })
// MongoDB cannot use the index for the sort because the range field
// (createdAt) appears before the sort field
// CORRECT index order following ESR:
// Equality: userId, status | Sort: createdAt | Range: createdAt
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })
// userId: equality filter → status: equality filter → createdAt: sort + range
// Another example:
// Query: { accountId: X, type: "purchase", amount: { $gt: 100 } }
// sorted by amount ascending
// ESR: accountId (E), type (E), amount (S+R)
db.transactions.createIndex({ accountId: 1, type: 1, amount: 1 })Index Selectivity
Selectivity measures how much of the collection an index filters out. A highly selective index on a field like email eliminates almost all documents immediately. A low-selectivity index on a field like isDeleted (where 95% of documents have false) provides almost no benefit — MongoDB may ignore it and do a COLLSCAN anyway.
Field | Selectivity | Index Value |
|---|---|---|
_id | Extremely high — unique per document | Excellent — jumps directly to one document |
High — typically unique or near-unique | Good — eliminates nearly all documents | |
country | Medium — ~200 distinct values globally | Moderate — still useful when combined in a compound index |
status ('active' / 'inactive') | Low — often 2-3 distinct values with skewed distribution | Often poor on its own; useful as a secondary field in a compound index |
isDeleted (95% false) | Very low — almost all documents have the same value | Poor — MongoDB may skip the index and COLLSCAN instead |
Covered Queries
A covered query is one where the index contains every field the query needs — both for filtering and for the projection returned to the client. MongoDB satisfies the entire query from the index without ever reading the actual documents from disk. This is the fastest possible query execution path.
Creating a covered query
// Index covers: email (filter), name and role (projection)
db.users.createIndex({ email: 1, name: 1, role: 1 })
// Query: find by email, return only name and role (exclude _id!)
db.users.find(
{ email: "alice@example.com" },
{ email: 1, name: 1, role: 1, _id: 0 } // must exclude _id
)
// Verify with explain() — look for NO "FETCH" stage
db.users.find(
{ email: "alice@example.com" },
{ email: 1, name: 1, role: 1, _id: 0 }
).explain("executionStats")
// Covered query explain output:
// {
// "winningPlan": {
// "stage": "PROJECTION_COVERED", // no FETCH stage!
// "inputStage": {
// "stage": "IXSCAN",
// "indexName": "email_1_name_1_role_1"
// }
// },
// "executionStats": {
// "totalDocsExamined": 0, // zero documents read from disk
// "totalKeysExamined": 1,
// "executionTimeMillis": 0
// }
// }
// IMPORTANT: including _id in the projection breaks covering
// unless _id is part of the index — _id is not in this index
db.users.find(
{ email: "alice@example.com" },
{ name: 1, role: 1 } // _id is included by default — NOT covered!
)Index Intersection
MongoDB can sometimes satisfy a query by combining two separate single-field indexes — this is called index intersection. For example, a query on { age: { $gt: 25 }, city: "Berlin" } could intersect an index on age with an index on city. However, index intersection has overhead and is generally less efficient than a single purpose-built compound index. When you identify a repeated multi-field query pattern, create a compound index rather than relying on intersection.
Identifying Unused Indexes
Find unused indexes
// $indexStats — usage statistics since last server restart
db.orders.aggregate([{ $indexStats: {} }])
// Example output:
// [
// {
// "name": "_id_",
// "key": { "_id": 1 },
// "accesses": { "ops": 48291, "since": ISODate("2025-01-01") }
// },
// {
// "name": "userId_1_createdAt_-1",
// "key": { "userId": 1, "createdAt": -1 },
// "accesses": { "ops": 12840, "since": ISODate("2025-01-01") }
// },
// {
// "name": "legacyField_1",
// "key": { "legacyField": 1 },
// "accesses": { "ops": 0, "since": ISODate("2025-01-01") }
// }
// ]
// "ops": 0 means the index has never been used since last restart
// — strong signal it can be dropped
// Filter to show only zero-usage indexes
db.orders.aggregate([
{ $indexStats: {} },
{ $match: { "accesses.ops": 0 } },
{ $project: { name: 1, key: 1 } }
])$indexStats after a representative traffic period (at least a few days) so low-frequency but important indexes are not mistakenly dropped.Index Bloat and Memory
// Total index memory footprint for a collection
const stats = db.orders.stats()
printjson(stats.indexSizes)
// {
// "_id_": 589824,
// "userId_1_createdAt_-1": 15728640,
// "status_1": 2097152
// }
print("Total index size:", stats.totalIndexSize, "bytes")
// WiredTiger cache usage — how much index data is in RAM
const serverStats = db.serverStatus()
printjson({
cacheSizeBytes: serverStats.wiredTiger.cache["maximum bytes configured"],
bytesInCache: serverStats.wiredTiger.cache["bytes currently in the cache"],
dirtyBytes: serverStats.wiredTiger.cache["tracked dirty bytes in the cache"]
})Working indexes — those accessed frequently by queries — should fit in RAM. If the total size of your active indexes exceeds available RAM, MongoDB must page index data to and from disk on each query, causing latency spikes. Monitor db.serverStatus().wiredTiger.cache to ensure your working set fits in the cache.
When NOT to Add an Index
Small collections (under ~10,000 documents) — a COLLSCAN on a small dataset is faster than the overhead of an index lookup
Write-heavy collections where the index maintenance cost outweighs the read benefit — e.g., high-throughput event ingestion tables
Fields with extremely low selectivity (boolean flags, status fields with 1-2 values) — standalone indexes on these are usually ignored by the query planner
Temporary or staging collections that are truncated and rebuilt frequently — the index build cost is paid every time
Fields that are only queried in analytics pipelines run off-peak — a full collection scan on a replica is often acceptable
The Performance Advisor (Atlas)
MongoDB Atlas Performance Advisor automatically analyzes slow queries (those exceeding 100ms) and recommends new indexes based on real traffic patterns. It shows the query shape, how many times it ran, the average execution time, and the specific index that would help. It also detects redundant indexes that overlap with existing ones. For production Atlas clusters, the Performance Advisor is the fastest and most reliable way to identify missing indexes.
Rolling Index Builds
For large production collections, building a new index can temporarily impact performance. Since MongoDB 4.2, index builds acquire locks only briefly at the start and end — they no longer block reads or writes for the full build duration. However, on very large collections, the build still consumes significant I/O and CPU.
Building indexes without blocking
// MongoDB 4.2+: index builds are interruptible and only briefly lock
// No special options needed — createIndex is safe to run on live collections
db.largeCollection.createIndex({ userId: 1, createdAt: -1 })
// Monitor index build progress
db.currentOp({ "command.createIndexes": { $exists: true } })
// Shows: progress.done, progress.total, msg (percentage)
// On Atlas, rolling index builds happen automatically for M10+ clusters:
// Atlas builds the index on one replica at a time, removing it from the
// replica set, building, then adding it back — zero downtime
// If you need to abort an in-progress index build:
db.killOp(<opid>) // opid from db.currentOp() output
// Check existing indexes while a build is in progress
db.largeCollection.getIndexes()
// The new index appears with a "building": true flag until complete{ a: 1, b: 1, c: 1 } already covers queries filtering on a, { a, b }, and { a, b, c } — no additional index is needed. Use explain() to confirm which index is actually chosen before creating a new one.