Beyond basic single-field indexes, MongoDB offers advanced indexing strategies — compound index ordering (ESR rule), covered queries, partial/sparse indexes, TTL indexes, and more — that dramatically affect query performance. Mastering them is key to optimizing MongoDB at scale.
For a compound index supporting a query with Equality, Sort, and Range conditions,
order the fields: EQUALITY → SORT → RANGE (the ESR rule).
// query: status = "active" (equality), sort by date (sort), age > 21 (range)
db.users.createIndex({ status: 1, date: 1, age: 1 }); // E, S, R order
db.users.find({ status: "active", age: { $gt: 21 } }).sort({ date: 1 });
// → the index serves equality match, sorted output, and range scan efficiently
The ESR rule (Equality, Sort, Range field order) builds compound indexes that serve a query's filter, sort, and range parts efficiently — a key optimization technique.
// if a query's filter AND returned fields are ALL in the index, no document fetch needed
db.users.createIndex({ email: 1, name: 1 });
db.users.find({ email: "[email protected]" }, { name: 1, _id: 0 }); // COVERED
// → answered entirely from the index (very fast — never touches the documents)
A covered query (all filter and projected fields in the index) is answered from the index alone — extremely fast, never reading documents.
PARTIAL → index only documents matching a filter (smaller, targeted)
createIndex({ email: 1 }, { partialFilterExpression: { active: true } })
SPARSE → index only documents that HAVE the field (skip missing)
TTL → auto-delete documents after a time (sessions, logs, temp data)
createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })
UNIQUE → enforce uniqueness (can combine with partial)
MULTIKEY → automatic for array fields (indexes each element)
WILDCARD → index unknown/arbitrary field names ({ "$**": 1 })
HASHED → for hashed sharding / even distribution
✓ explain("executionStats") → verify index use (IXSCAN), check docs examined vs returned
✓ $indexStats → find UNUSED indexes (remove them — they slow writes for no benefit)
✓ Atlas Performance Advisor → suggests indexes based on actual query patterns
✓ Balance: enough indexes for reads, not so many that writes suffer
Advanced indexing strategies are valuable senior-level knowledge for optimizing MongoDB performance at scale, building on basic indexing.
While basic indexes help, mastering advanced strategies is what separates adequate from excellent MongoDB performance.
The ESR rule (ordering compound index fields as Equality, Sort, Range) is a key technique for building compound indexes that efficiently serve a query's filter, sort, and range conditions together — a frequently-applicable optimization that's non-obvious without knowing the rule. Covered queries (where all filter and returned fields are in the index, so the query is answered from the index alone without reading documents) provide dramatic speedups for read-heavy queries — a powerful technique.
Knowing the specialized index types — partial (indexing only matching documents, smaller and targeted), sparse (only documents with the field), TTL (auto-expiring data, ideal for sessions/logs/temp data), wildcard (for unknown field names), multikey (automatic for arrays), and unique — lets you choose the right index for each scenario, optimizing storage and write cost while serving queries.
The analysis and maintenance discipline (using explain() to verify index use and efficiency, $indexStats to find and remove unused indexes that needlessly slow writes, and tools like Atlas Performance Advisor) is essential for keeping indexing effective over time.
Since indexing is the most impactful MongoDB performance lever, and since advanced strategies (ESR compound ordering, covered queries, specialized index types, and maintenance) are what optimize it at scale, understanding advanced indexing strategies is valuable senior-level knowledge for high-performance MongoDB, a topic demonstrating deep optimization expertise expected for senior roles and one where techniques like the ESR rule and covered queries provide significant, practical performance gains.