MongoDB Query & Index Optimization

1. The ESR Rule

Primary compound index design framework: Equality → Sort → Range. Place predicate types in this order to minimize index bounds and avoid blocking operations.

Predicate Types

Sub-Rule: E Before S

Equality before sort avoids blocking in-memory SORT stage.

BAD EXAMPLE

Sort field before equality: forces in-memory sort

Bad Index 
db.users.createIndex({ createdAt: -1, status: 1 })
// totalKeysExamined: 1000, SORT stage present

GOOD EXAMPLE

Equality before sort: index supports sort order

Good Index 
db.users.createIndex({ status: 1, createdAt: -1 })
// totalKeysExamined: 120, no SORT stage

Sub-Rule: E Before R

Equality narrows key space before range scanning begins.

BAD EXAMPLE

Range before equality: scans all range values first

Bad Index 
db.users.createIndex({ age: 1, status: 1 })
// totalKeysExamined: 500

GOOD EXAMPLE

Equality first: narrows to 120 matching status before range scan

Good Index 
db.users.createIndex({ status: 1, age: 1 })
// totalKeysExamined: 120

Sub-Rule: S Before R

Range before sort forces blocking sort even with an index. After a range scan, index order is not guaranteed to match sort requirements.

BAD EXAMPLE

Bad Index 
db.orders.createIndex({ status: 1, total: 1, createdAt: -1 })
// Query: status="shipped", total>100, sort createdAt:-1
// Explain: SORT stage present

GOOD EXAMPLE

Good Index 
db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })
// Query: status="shipped", total>100, sort createdAt:-1
// Explain: IXSCAN only, no SORT stage

Multiple Equality Fields

Order among equality fields does not matter. B-Tree leaf pages store all combinations equally, and selectivity among E fields is irrelevant to key count.

2. Index Types

Single Field Index

Index on a single document field. Use for frequent single-field filters/sorts. Not suitable for compound queries.

Syntax 
db.collection.createIndex({ fieldName: 1 }) // 1=ascending, -1=descending

Compound Index

Index on multiple fields, follows ESR rule. Use for queries with multiple filter/sort/range fields.

Syntax 
db.collection.createIndex({ equality1: 1, equality2: 1, sort1: -1, range1: 1 })

Multikey Index

Auto-created by MongoDB when indexing array fields. Only one array field per compound index allowed. Performance cost: index size grows with array cardinality.

Syntax 
db.collection.createIndex({ tags: 1 }) // tags is an array field

Text Index

Supports text search with scoring. One text index per collection, case-insensitive. Use hint() to force usage.

Syntax 
db.collection.createIndex({ content: "text" })
db.collection.find({ $text: { $search: "mongodb" } }).hint({ content: "text" })

Hashed Index

Good for hash-based sharding, equality only. Cannot support range queries.

Syntax 
db.collection.createIndex({ _id: "hashed" })

Wildcard Index

Use for dynamic/unpredictable field names. Not suitable for high-cardinality known fields.

Syntax 
db.collection.createIndex({ "$**": 1 })

Partial Index

Only indexes documents matching a filter expression. Saves storage and write overhead. Example: index only active users.

Syntax 
db.users.createIndex({ email: 1 }, { partialFilterExpression: { status: "active" } })

Sparse Index

Only indexes documents where the field exists. Differs from partial index: partial uses custom filter, sparse only checks field existence.

Syntax 
db.users.createIndex({ phoneNumber: 1 }, { sparse: true })

TTL Index

Auto-deletes documents after expiry. Only on Date fields, single field only. Background deletion runs every 60 seconds.

Syntax 
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

Geospatial Indexes

2dsphere for GeoJSON, 2d for legacy coordinates. Use with $near, $geoWithin, $geoIntersects.

Syntax 
db.places.createIndex({ location: "2dsphere" })

3. Index Selectivity

Selectivity = ratio of matching documents to total documents. High selectivity (fewer matches) = better index efficiency.

4. Covered Queries

Query satisfied entirely by index, no document fetch. Requirements: all queried fields AND projected fields must be in the index.

BAD EXAMPLE

Missing projection field in index, causes FETCH stage

Query 
db.users.find({ status: "active" }, { name: 1, email: 1, _id: 0 })
// Index: { status: 1, name: 1 }
// Explain: FETCH stage present

GOOD EXAMPLE

All projected fields in index, eliminates FETCH stage

Good Index 
db.users.createIndex({ status: 1, name: 1, email: 1 })
// Explain: IXSCAN only, no FETCH stage

Note: _id must be explicitly excluded if not in index. Only optimize for covered queries if the query is high-frequency.

5. Query Planner and Explain()

Query planner selects candidate plans, caches winning plan. Invalidate plan cache with index changes or collection modifications.

Explain Modes

Key Explain Fields

Use hint() to force index usage: db.collection.find(query).hint({ indexName: 1 })

6. Aggregation Pipeline Optimization

7. Write Performance vs Index Count

8. Common Query Anti-Patterns

9. Schema Design Impact on Query Performance

10. Working Set and Memory

Working set = most frequently accessed data and indexes. Should fit in WiredTiger cache (RAM).

11. Quick-Reference Index Design Checklist

  • ☐ Have you classified every field as E, S, or R?
  • ☐ Are equality fields first?
  • ☐ Are sort fields before range fields?
  • ☐ Is the index selective enough to be worth it?
  • ☐ Will this create a covered query opportunity?
  • ☐ Does an existing index already cover this query (redundancy check)?
  • ☐ What write overhead does this add?
  • ☐ Have you run explain() in executionStats mode to verify?

12. Common Mistakes Quick Reference

Mistake Why it hurts Fix
Leading wildcard regex Can't use index, full scan Use anchored regex or text index
$where/$function JS execution, always COLLSCAN Rewrite to native operators
Negation operators Scans all non-matching docs Restructure query to use equality
Deep pagination with skip() O(n) scan for large offsets Use keyset pagination
No projections Fetches unnecessary data Always project required fields
Type mismatch in query Misses index hits Match field type in query
Speculative indexes Slows writes unnecessarily Only create indexes for proven queries
Redundant indexes Wastes storage and write overhead Drop indexes covered by compound indexes

13. Sharding and Shard Key Selection

Sharding distributes data across multiple servers for horizontal scaling. Only shard when single-node resources (CPU, RAM, storage) are exhausted — premature sharding adds operational complexity.

Shard Key Selection Criteria

Targeted vs Scatter-Gather Queries

Queries without the shard key broadcast to all shards (scatter-gather), adding latency. Queries with the shard key route to a single shard (targeted).

BAD EXAMPLE

Query without shard key: mongos fans out to all shards

Query 
db.orders.find({ createdAt: { $gt: ISODate("2026-01-01") } })
// Shard key: { userId: 1 }
// Explain: SHARD_MERGE stage present, broadcast to all shards

GOOD EXAMPLE

Query includes shard key prefix: targeted to single shard

Query 
db.orders.find({ userId: "user123", createdAt: { $gt: ISODate("2026-01-01") } })
// Explain: Single shard plan, no SHARD_MERGE

Hashed Shard Keys

Good for write distribution, bad for range queries. Use when you need even writes but don't query ranges on the shard key.

Syntax 
sh.shardCollection("db.orders", { userId: "hashed" })

Compound Shard Keys

Combine low-cardinality + high-cardinality fields for balance. Example: { region: 1, userId: 1 } (region is low-cardinality, userId high-cardinality).

Zone Sharding

Pin data ranges to specific shards for data locality (e.g., EU user data on EU-based shards).

Syntax 
sh.addShardToZone("shard1", "EU")
sh.updateZoneKeyRange("db.users", { region: "EU" }, { region: "EU" }, "EU")

Resharding

Available since MongoDB 5.0. Use to change shard key or rebalance data. Cost: requires temporary storage and can impact performance during execution.

Rule: Shard Key in Compound Indexes

Always include the shard key (or its prefix) in compound indexes on sharded collections to enable targeted index scans.

14. Read Preferences and Replica Set Routing

Read preferences control which replica set member serves read operations. Five modes:

When to Use Secondary Reads

Stale Read Risk

Secondary replication lag means data may not be current. Never use secondary reads for read-your-own-writes — use causal consistency sessions instead.

Causal Consistency Sessions

Guarantees monotonic reads across replica set members. Sessions track the last write operation and wait for secondaries to catch up.

Node.js Example 
const session = client.startSession({ causalConsistency: true });
await db.collection.insertOne({ x: 1 }, { session });
const result = await db.collection.findOne({ x: 1 }, { session });

Nearest Mode

Routes to lowest-latency node, good for globally distributed deployments but carries staleness risk from replication lag.

Member Tagging

Route specific query types to dedicated nodes. Example: tag a secondary for analytics workloads.

Python Example 
client = MongoClient(
  "mongodb://host1,host2,host3/?readPreference=secondary&readPreferenceTags=workload:analytics"
)

15. Connection Pooling and Driver Configuration

Connection pooling reuses TCP connections to avoid expensive per-query handshake overhead. Default pool size is 100 across most drivers.

Key Pool Settings

Pool Exhaustion Symptoms

Queued operations, latency spikes, "connection pool timed out" errors.

Rule: Singleton MongoClient

Never create a new MongoClient per request — reuses the same pool across the application process.

BAD EXAMPLE

New client per request handler

Node.js (Bad) 
app.get("/users", async (req, res) => {
  const client = new MongoClient(uri); // BAD: new client per request
  await client.connect();
  const users = await client.db().users.find().toArray();
  res.json(users);
});

GOOD EXAMPLE

Module-level singleton client

Node.js (Good) 
// mongo-client.js
const client = new MongoClient(uri);
module.exports = client;

// handler.js
const client = require("./mongo-client");
app.get("/users", async (req, res) => {
  const users = await client.db().users.find().toArray();
  res.json(users);
});
Python (Good) 
# mongo_client.py
client = MongoClient(uri)

# handler.py
from mongo_client import client
def get_users():
    return list(client.db.users.find())

16. WiredTiger Storage Engine Internals

WiredTiger is MongoDB's default storage engine, using B-Tree pages for document and index storage.

WiredTiger Cache

Defaults to 50% of RAM minus 1GB. This is your working set limit — data/indexes outside the cache incur disk I/O.

Check Cache Size 
db.serverStatus().wiredTiger.cache["maximum bytes configured"]

Checkpoints

Dirty data flushed to disk every 60 seconds or when the journal fills. Checkpoint spikes can cause temporary latency.

Compression

Default is snappy (fast, moderate compression). Alternatives: zlib (higher compression, more CPU), zstd (best balance, MongoDB 4.2+).

Concurrency Tickets

Read/write tickets control concurrent transactions (default 128 each). Sustained values below 128 indicate server saturation. MongoDB 7.0+ uses dynamic ticket adjustment.

Document-Level Locking

WiredTiger locks at document level, not collection level — enables high concurrent write throughput for distinct documents.

Cache Eviction

When cache fills, eviction threads remove cold data. Monitor eviction rate to detect working set exceeding cache size.

Key Metrics 
db.serverStatus().wiredTiger.cache["bytes currently in the cache"]
db.serverStatus().wiredTiger.cache["pages read into cache"]
db.serverStatus().wiredTiger.concurrentTransactions.read.available
db.serverStatus().wiredTiger.concurrentTransactions.write.available

17. Monitoring and the Database Profiler

Self-Hosted Profiling

Profiler logs slow or all operations to the system.profile collection.

Profiling Levels 
db.setProfilingLevel(0) // Off
db.setProfilingLevel(1, { slowms: 100 }) // Slow ops >100ms
db.setProfilingLevel(2) // All ops (NEVER in production)

Reading system.profile

Key fields: op (operation type), ns (namespace), millis (duration), keysExamined, docsExamined, planSummary.

Query Slow Ops 
db.system.profile.find({ millis: { $gt: 100 } }).sort({ ts: -1 }).limit(10)

Profiler Overhead

Level 2 writes to system.profile for every op — only use temporarily on a secondary.

Persistent Slow Query Logging

Set slowOpThresholdMs in mongod.conf instead of relying on Level 1 profiling for production.

MongoDB Atlas Tools

Key Metrics to Track

Find Zero-Ops Indexes

Aggregation 
db.collection.aggregate([{ $indexStats: {} }])
// Look for indexes with "accesses.ops": 0 after a full query cycle

18. Transactions and Multi-Document Operations

Multi-document transactions provide ACID guarantees across multiple documents/collections — but add significant performance cost.

Performance Cost

Best Practices

transactionLifetimeLimitSeconds defaults to 60s — transactions exceeding this are aborted automatically.

Sharded Cluster Transactions

Distributed transactions add coordinator overhead — even more reason to keep them small.

BAD EXAMPLE

Reading, transforming, and writing 50 documents in one transaction

Transaction (Bad) 
const session = client.startSession();
session.startTransaction();
const docs = await db.orders.find({ status: "pending" }, { session }).toArray();
const updated = docs.map(d => ({ ...d, status: "processed" }));
await db.orders.insertMany(updated, { session });
await session.commitTransaction();

GOOD EXAMPLE

Compute outside the transaction, write only final result inside

Transaction (Good) 
const docs = await db.orders.find({ status: "pending" }).toArray();
const updated = docs.map(d => ({ ...d, status: "processed" }));
const session = client.startSession();
session.startTransaction();
await db.orders.insertMany(updated, { session });
await session.commitTransaction();

19. Pagination Patterns

Why skip() Is Dangerous at Scale

MongoDB must scan and discard (page × pageSize) documents before returning results. Page 1000 with pageSize 20 scans 20,000 docs.

BAD EXAMPLE

skip() for page 1000, pageSize 20

Query (Bad) 
db.users.find().sort({ _id: 1 }).skip(1000 * 20).limit(20)
// explain(): totalDocsExamined = 20000

Keyset Pagination (Cursor-Based)

Use the last seen value of an indexed field as the anchor for the next page instead of skip(). Works best with unique monotonic fields (_id, timestamp + _id compound).

GOOD EXAMPLE

Query (Good) 
db.users.find({ _id: { $gt: lastSeenId } }).sort({ _id: 1 }).limit(20)
// explain(): totalDocsExamined = 20

Limitation: Cannot jump to arbitrary pages — only forward/backward navigation.

$facet for Paginated Counts

Combine $count and $skip/$limit in a single pipeline pass for total count + paginated results. Use when you need total page count, but avoid for high-traffic endpoints due to $facet memory overhead.

Aggregation 
db.users.aggregate([
  { $facet: {
    total: [{ $count: "count" }],
    results: [{ $sort: { _id: 1 } }, { $skip: 20 }, { $limit: 20 }]
  } }
])

Infinite Scroll vs Traditional Pagination

Keyset pagination is natural for infinite scroll. Traditional offset pagination needs special handling to avoid duplicate documents when new data is inserted during navigation.

20. Caching Strategies with MongoDB

Add a cache layer for expensive, read-heavy queries returning data that doesn't change on every request.

Redis Cache Patterns

Cache-Aside (Most Common)

App checks Redis first, falls back to MongoDB, writes result to Redis with TTL.

Node.js Example 
async function getUserProfile(userId) {
  const cached = await redis.get(`user:${userId}`);
  if (cached) return JSON.parse(cached);
  const user = await db.users.findOne({ _id: userId });
  await redis.set(`user:${userId}`, JSON.stringify(user), "EX", 3600);
  return user;
}

Write-Through

Write to both MongoDB and Redis on update to guarantee consistency.

What to Cache (and Not)

Cache Invalidation

MongoDB Internal Caches

21. Index Build Performance

MongoDB 4.4+ Index Builds

Hybrid approach: brief exclusive lock at start/end, concurrent reads/writes in between. Safe for production on large collections.

Before 4.4: Foreground builds blocked all operations — never run in production.

Rolling Index Builds (Replica Sets)

Build index on each secondary one at a time while primary stays live. Step down primary, build on it last. Avoids replication lag spikes for large collections.

Index Builds on Sharded Clusters

Each shard builds independently. Use rolling approach per shard for large collections.

Monitor Index Build Progress

Check Running Builds 
db.currentOp({ "command.createIndexes": { $exists: true } })

Hide Index Before Dropping

Hidden indexes are not used by the query planner but still maintained. Validate nothing breaks before permanent drop — safer than immediate drop.

Syntax 
db.collection.hideIndex("indexName")
// Monitor for issues, then:
db.collection.dropIndex("indexName")

Schedule During Off-Peak Hours

Index builds consume I/O and CPU — schedule on low-traffic windows. On Atlas: use rolling index build option in the UI.

22. Unbound Arrays and Large Document Anti-Patterns

Unbound Arrays

Arrays with no upper size limit that grow over time. Risks:

Detection: Monitor avg document size with db.collection.stats().avgObjSize.

Bucket Pattern

Group time-series or event data into fixed-size bucket documents instead of one document per event.

BAD SCHEMA

One document per event (unbound array of events per user)

Bad Schema 
{
  userId: "user123",
  events: [
    { type: "click", ts: ISODate("2026-01-01") },
    // ... grows unbound
  ]
}

GOOD SCHEMA

Fixed-size bucket documents (100 events per bucket)

Good Schema 
{
  userId: "user123",
  bucketStart: ISODate("2026-01-01"),
  events: [ /* up to 100 events */ ],
  count: 100
}
// Query: db.events.find({ userId: "user123", bucketStart: { $gt: ... } })

Outlier Pattern

Handle rare documents with massive arrays separately to avoid degrading performance for typical cases.

Large $in Arrays

Query planner treats each value as a separate scan. Over ~100-200 values, performance degrades significantly — break into batches or restructure the query.

Document Size Monitoring

Spot Check 
Object.bsonsize(db.collection.findOne()) // Single doc size
db.collection.stats().avgObjSize // Avg collection doc size

23. Time-Series Collections

Specialized collection type optimized for append-mostly, time-ordered data (IoT sensors, metrics, logs, financial tick data).

Internal Structure

MongoDB automatically clusters documents into compressed bucket documents (not visible to the app) aligned with time-range access patterns.

Required Fields

Create Time-Series Collection 
db.createCollection("metrics", {
  timeseries: {
    timeField: "timestamp",
    metaField: "sensorId",
    granularity: "hours"
  }
})

Query Performance

Range queries on timeField are extremely fast. Add secondary indexes on metaField for filtered time-range queries.

Windowed Aggregations

Use $setWindowFeatures for moving averages, running totals. Use $densify to fill gaps in sparse time series.

Moving Average Example 
db.metrics.aggregate([
  { $setWindowFields: {
    partitionBy: "$sensorId",
    sortBy: { timestamp: 1 },
    output: { movingAvg: { $avg: "$value", window: { range: [-5, 0] } } }
  } }
])

TTL on Time-Series

Set expireAfterSeconds at collection creation to auto-expire old buckets — more efficient than TTL on regular collections.

Limitations

24. Query Shapes and Plan Cache

A query shape is the structure of a query without its literal values — same shape = same cached plan.

How the Plan Cache Works

Plan Cache Invalidation Triggers

Manage Plan Cache

View Cached Plans 
db.collection.getPlanCache().list()
Clear Plan Cache (Use with Caution) 
db.collection.getPlanCache().clear()

Stale Cached Plans

Data distribution changes significantly but cache isn't invalidated — old plan may be suboptimal. Use explain("allPlansExecution") to manually re-evaluate.

$planCacheStats Aggregation

Collection-level view of all cached plans, their hit counts, and whether they are active.

Aggregation 
db.collection.aggregate([{ $planCacheStats: {} }])

hint() as Last Resort

Force a specific index when the planner consistently picks the wrong plan. Always document why the hint is needed.

Syntax 
db.collection.find(query).hint({ indexName: 1 })

Interactive Tools

ESR Classifier Tool

Enter your query fields to get the recommended ESR index order.

Recommended Index 

      


      
      

        
Explain Plan Reader

        
Enter execution stats to diagnose query performance.

        
        
        
        

          
          
          
          
          
        

        
        

      


      
      

        
Shard Key Evaluator

        
Input field details to score shard key suitability.

        
        
        
        
        
        

      


      
      

        
Pagination Cost Calculator

        
Compare docs scanned for skip() vs keyset pagination.

        
        
        
        
        

      


      
      

        
Transaction Complexity Checker

        
Check if a transaction is appropriate for your use case.