Index Strategy and Query Planner Validation
Learners will define the full index set for the schema, validate each index with explain(), and prune redundant indexes.
Index Strategy and Query Planner Validation is a free MongoDB Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the MongoDB Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Index Strategy Overview
An index strategy is a deliberate plan for which indexes to create, not an ad-hoc collection of indexes added whenever a query is slow. Every index has a cost: it speeds up reads but slows down writes (each write must update all indexes on the collection) and consumes RAM (indexes must fit in the working set). A good strategy creates the minimum number of indexes that cover all high-frequency access patterns.
Start With the Access Pattern Register
Map every high-frequency access pattern identified during requirements analysis to a proposed index. Document the index fields, sort direction, and purpose. For our e-commerce platform: the product page uses { slug: 1 }; the category listing uses { categoryId: 1, price: 1, _id: 1 } for keyset pagination; the order history uses { userId: 1, createdAt: -1 }. This register prevents redundant indexes and missed coverage.
// Index register for e-commerce capstone
const indexRegister = [
{ collection: 'products', index: { slug: 1 }, unique: true, covers: 'product page' },
{ collection: 'products', index: { categoryId: 1, price: 1, _id: 1 }, unique: false, covers: 'category listing + keyset' },
{ collection: 'products', index: { 'vendor._id': 1 }, unique: false, covers: 'vendor store page' },
{ collection: 'orders', index: { userId: 1, createdAt: -1 }, unique: false, covers: 'user order history' },
{ collection: 'orders', index: { status: 1, createdAt: 1 }, unique: false, covers: 'fulfillment queue' },
{ collection: 'reviews', index: { productId: 1, createdAt: -1 }, unique: false, covers: 'reviews by product' }
]Applying the ESR Rule to Compound Indexes
The ESR rule (Equality → Sort → Range) determines field order in compound indexes. Place equality filter fields first (they reduce the candidate set most), sort fields next (so MongoDB can serve the sort from the index), and range filter fields last. This ordering maximises index coverage and lets MongoDB avoid an in-memory sort stage.
// Query: products in category, price under 200, sorted by price
// E = categoryId (equality), S = price (sort), R = none
// Correct ESR order:
db.products.createIndex({ categoryId: 1, price: 1 })
// Query: orders by user, status = 'processing', sorted by date
// E = userId (equality) + status (equality), S = createdAt
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })
// Verify with explain
db.orders.find({ userId: userId, status: 'processing' })
.sort({ createdAt: -1 })
.explain('executionStats')Creating the Index Set for the Capstone
Create all planned indexes in a single script so they can be applied atomically to any environment (local, staging, production). Run index creation in the background ({ background: true } in older versions; background by default in MongoDB 4.2+) so it does not block the collection during creation. Always test index creation on a staging environment before running on production data.
// indexes/setup.js — run once per environment
async function createIndexes(db) {
await db.collection('products').createIndexes([
{ key: { slug: 1 }, unique: true },
{ key: { categoryId: 1, price: 1, _id: 1 } },
{ key: { tags: 1 } },
{ key: { 'vendor._id': 1 } }
])
await db.collection('orders').createIndexes([
{ key: { userId: 1, createdAt: -1 } },
{ key: { status: 1, createdAt: 1 } },
{ key: { 'items.productId': 1 } }
])
await db.collection('reviews').createIndexes([
{ key: { productId: 1, createdAt: -1 } },
{ key: { userId: 1 } }
])
console.log('All indexes created')
}Validating Indexes With explain()
After creating indexes, validate each critical query using .explain('executionStats'). Look for four key fields in the output: winningPlan.inputStage.stage should be 'IXSCAN' (not 'COLLSCAN'); totalKeysExamined should be close to nReturned; totalDocsExamined should equal nReturned for a covered query; and executionTimeMillis should be acceptably low.
// Validate product category listing query
const result = await db.collection('products')
.find({ categoryId: new ObjectId('...'), price: { $lte: 200 } })
.sort({ price: 1 })
.explain('executionStats')
const { winningPlan, totalKeysExamined, totalDocsExamined, nReturned, executionTimeMillis } = result.executionStats
console.log('Stage:', winningPlan.inputStage.stage) // IXSCAN or COLLSCAN
console.log('Keys / Docs / Returned:', totalKeysExamined, totalDocsExamined, nReturned)
console.log('Time ms:', executionTimeMillis)Covered Queries: Eliminating FETCH
A covered query is one where all projected fields are available in the index itself — MongoDB never needs to fetch the actual document. Covered queries are extremely fast because they read only the index (smaller, fits in RAM) rather than loading documents. For the product listing page that needs only slug, name, price, and imageUrl, create an index that includes all these fields.
// Covered index for product listing cards
db.products.createIndex({
categoryId: 1,
price: 1,
name: 1,
slug: 1,
imageUrl: 1
})
// This query is now covered — no FETCH stage
db.products.find(
{ categoryId: ObjectId('...') },
{ _id: 0, name: 1, slug: 1, price: 1, imageUrl: 1 }
).sort({ price: 1 }).explain('executionStats')
// Verify: no FETCH stage in winningPlanIdentifying Redundant Indexes
Indexes are expensive — each one adds write overhead. A redundant index is one whose prefix is identical to another index. If you have { userId: 1 } and { userId: 1, createdAt: -1 }, the first is redundant because the compound index satisfies all queries that the single-field index would. Use db.collection.aggregate([{ $indexStats: {} }]) to see which indexes have low access counts and consider dropping them.
// Find rarely used indexes
db.orders.aggregate([{ $indexStats: {} }])
.then(stats => {
stats.forEach(s => {
console.log(s.name, 'accesses:', s.accesses.ops)
})
})
// Indexes with ops = 0 since last restart may be candidates for removal
// Drop a redundant index
db.orders.dropIndex('userId_1') // if userId_1_createdAt_-1 already covers itThe Text Index for Product Search
Full-text search on product names, descriptions, and tags requires a text index. Create a compound text index covering all searchable string fields. Add a weights option to rank name matches higher than description matches. Validate that $text queries use TEXT stage in explain and return results sorted by textScore.
// Text index for product search
db.products.createIndex(
{ name: 'text', description: 'text', tags: 'text' },
{ weights: { name: 10, tags: 5, description: 1 }, name: 'product_text_idx' }
)
// Text search query with relevance sorting
db.products.find(
{ $text: { $search: 'wireless noise cancelling' } },
{ score: { $meta: 'textScore' } }
).sort({ score: { $meta: 'textScore' } }).limit(20)Sparse and Partial Indexes for Optional Fields
Some documents have optional fields that only a subset of documents carry. Create a partial index with partialFilterExpression to index only the documents where the field exists and meets the condition. This keeps the index small and efficient compared to indexing null values in a sparse index. For orders with a couponCode field (present on only 10% of orders), a partial index is ideal.
// Partial index: only index orders that have a coupon
db.orders.createIndex(
{ couponCode: 1 },
{
partialFilterExpression: { couponCode: { $exists: true } },
name: 'orders_with_coupon'
}
)
// Partial index: only index active products
db.products.createIndex(
{ categoryId: 1, price: 1 },
{
partialFilterExpression: { isActive: true },
name: 'active_products_by_category'
}
)Unique Indexes for Data Integrity
Unique indexes prevent duplicate data at the database level — the safest place to enforce uniqueness. Create unique indexes on fields that must be unique: user.email, product.slug, vendor.slug. A unique index is more reliable than application-level checks because it prevents duplicates even under race conditions or concurrent writes from multiple application instances.
// Unique indexes for business constraints
db.users.createIndex({ email: 1 }, { unique: true })
db.products.createIndex({ slug: 1 }, { unique: true })
db.vendors.createIndex({ slug: 1 }, { unique: true })
// Partial unique index: unique email only for verified users
db.users.createIndex(
{ email: 1 },
{
unique: true,
partialFilterExpression: { emailVerified: true }
}
)Index Maintenance and Monitoring
Indexes need ongoing maintenance. Use MongoDB Compass or Atlas Performance Advisor to monitor query performance and receive index suggestions automatically. The Performance Advisor analyses slow queries (those exceeding the slowMs threshold) and suggests compound indexes to improve them. Periodically review $indexStats to prune indexes that are no longer being used as query patterns evolve.
// Enable profiling to capture slow queries
db.setProfilingLevel(1, { slowms: 50 }) // log queries > 50ms
// Query the profiler for the slowest recent operations
db.system.profile.find().sort({ millis: -1 }).limit(10).pretty()
// Check index usage statistics (reset on mongod restart)
db.products.aggregate([{ $indexStats: {} }])Quick Check
Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.
Lesson Recap
In this lesson you learned: the ESR rule (Equality → Sort → Range) determines the optimal field order in compound indexes for maximum coverage, explain('executionStats') validates that queries use IXSCAN and reveals the documents examined vs returned ratio, and $indexStats identifies redundant or unused indexes that should be pruned to reduce write overhead. Next up we draft the scaling plan — from replica set to sharded cluster.
Frequently asked questions
Is the “Index Strategy and Query Planner Validation” lesson free?
Yes — the full text of “Index Strategy and Query Planner Validation” is free to read here on the web, and the MongoDB Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the MongoDB Academy course, upgrade to CoddyKit PRO.
What will I learn in “Index Strategy and Query Planner Validation”?
Learners will define the full index set for the schema, validate each index with explain(), and prune redundant indexes. You practise MongoDB Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start MongoDB Academy?
No prior experience is required. MongoDB Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Index Strategy and Query Planner Validation” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this MongoDB Academy lesson?
Yes. Every MongoDB Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Requirements Analysis and Schema Design
- Index Strategy and Query Planner Validation
- Scaling Plan: Replica Set to Sharded Cluster
- Security Hardening and Production Checklist