Indexes in MongoDB
Single-field, compound, and special indexes
Skills taught: mongodb.indexes
Why Indexes Matter
Without an index, MongoDB performs a COLLSCAN (collection scan) — reading every document to find matches. With an index, MongoDB does an IXSCAN (index scan) — jumping directly to matching documents. The difference between these two for large collections can be the difference between milliseconds and minutes.
// Before index: COLLSCAN — reads every document
const user = await users.findOne({ email: 'alice@example.com' });
// On 1M documents: ~300ms
// Create the index
await users.createIndex({ email: 1 });
// After index: IXSCAN — jumps directly to 'alice'
const user2 = await users.findOne({ email: 'alice@example.com' });
// On 1M documents: ~1ms
// Verify with explain()
const plan = await users.find({ email: 'alice@example.com' }).explain('executionStats');
console.log(plan.queryPlanner.winningPlan.stage); // FETCH (good)
console.log(plan.queryPlanner.winningPlan.inputStage.stage); // IXSCAN (good)
console.log(plan.executionStats.totalDocsExamined); // 1 (excellent!)
Creating Indexes
// Single-field index (1 = ascending, -1 = descending)
await users.createIndex({ email: 1 });
await orders.createIndex({ placedAt: -1 }); // descending — for newest-first queries
// Unique index — rejects duplicate values
await users.createIndex({ email: 1 }, { unique: true });
// Compound index — covers multi-field queries
// Use ESR rule: Equality, Sort, Range
await products.createIndex({ category: 1, price: -1 });
// Covers: find({ category: 'Electronics' }).sort({ price: -1 })
// Sparse index — only indexes documents where the field exists
await users.createIndex({ phone: 1 }, { sparse: true });
// Documents without 'phone' are excluded from this index
// TTL index — auto-delete after N seconds
await sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 86400 });
// Partial index — index only documents matching a filter
await orders.createIndex(
{ customerId: 1, placedAt: -1 },
{ partialFilterExpression: { status: 'active' } } // only index active orders
);
// Smaller and faster than a full index on these fields
The ESR Rule for Compound Indexes
When building compound indexes, follow the ESR rule: put Equality fields first, Sort fields second, Range fields last. This ordering makes compound indexes most efficient.
// Query: find active Electronics products, sorted by price, filtered to price < 5000
const q = products.find(
{ status: 'active', category: 'Electronics', price: { $lt: 5000 } }
).sort({ price: 1 });
// BAD index order:
await products.createIndex({ price: 1, status: 1, category: 1 });
// Range field first — MongoDB can't use the index efficiently for status/category
// GOOD index order (ESR):
// Equality: status=active, category=Electronics (exact matches first)
// Sort: price (sort field next)
// Range: price < 5000 (range field last)
await products.createIndex({ status: 1, category: 1, price: 1 });
// Now MongoDB scans the minimum index range needed
// A compound index (a, b, c) also covers queries on:
// (a), (a, b), (a, b, c)
// But NOT (b), (c), (b, c) — the prefix rule applies
Tip: A compound index (a, b, c) can serve queries that use any LEFT PREFIX: (a), (a,b), or (a,b,c). It cannot serve queries starting with b or c alone. Design your compound indexes to cover your most common query patterns.
Analyzing Queries with explain()
// Get the execution plan for a query
const result = await products
.find({ category: 'Electronics', price: { $lt: 2000 } })
.sort({ price: 1 })
.explain('executionStats'); // 'queryPlanner' for plan only
// Key fields to check:
console.log('Winning plan stage:', result.queryPlanner.winningPlan.stage);
// IXSCAN = index scan (good)
// COLLSCAN = collection scan (bad — add an index!)
// FETCH = fetching documents after index lookup (normal)
// COVERED = no document fetch needed (best!)
console.log('Docs examined:', result.executionStats.totalDocsExamined);
console.log('Keys examined:', result.executionStats.totalKeysExamined);
console.log('Docs returned:', result.executionStats.nReturned);
// Ideal: docsExamined == nReturned (no wasted work)
// Red flag: docsExamined >> nReturned (index not selective enough)
console.log('Time (ms):', result.executionStats.executionTimeMillis);
// In mongosh (simpler):
db.products.find({ category: 'Electronics' }).explain('executionStats')
Managing Indexes
// List all indexes on a collection
const indexes = await products.indexes();
console.log(indexes);
// Always includes: { v: 2, key: { _id: 1 }, name: '_id_' }
// Drop a specific index by name
await products.dropIndex('category_1_price_-1');
// Drop all indexes except _id
await products.dropIndexes();
// Check index usage statistics (see which indexes are actually used)
const stats = await db.command({
aggregate: 'products',
pipeline: [{ $indexStats: {} }],
cursor: {}
});
// Shows accesses.ops for each index — if 0, the index is unused
Warning: Every index slows write operations (insert, update, delete) because all indexes must be updated for every write. A collection with 10 indexes takes ~10x more time to insert into than the same collection with 0 indexes. Only create indexes you actually query against.
Frequently Asked Questions
How many indexes should a collection have?
Create only the indexes your queries actually need. Use explain() to verify index usage, and use $indexStats to find unused indexes. A good rule: 5–10 indexes per collection is typical; more than 15 starts to hurt write performance noticeably.
Does MongoDB always use an index if one exists?
No. MongoDB's query planner chooses the fastest plan. If the index is not selective enough (e.g., a boolean field with 50% true), MongoDB may choose a COLLSCAN. Use explain() to see which plan was chosen and hint() to force a specific index.
What is a covered query?
A covered query is one where all fields in the query filter AND the projection are in the index — MongoDB never reads the actual document. For example, createIndex({ email: 1, name: 1 }) covers find({ email: ... }, { name: 1, _id: 0 }). Covered queries are the fastest possible queries in MongoDB.