MongoDB Indexing Strategies – Complete Guide

Indexing is one of the most critical performance factors in MongoDB. Proper indexing can speed up queries by 100x or more, while poor indexing leads to slow performance, high CPU, and full collection scans.

Why Indexes Matter

1. Types of Indexes in MongoDB

2. The ESR (Equality → Sort → Range) Rule

Golden Rule for Compound Indexes:

Equality → Sort → Range

Why?

MongoDB uses one index per query. The index must support:
1. Equality filters first (exact match)
2. Sort (if any)
3. Range filters (like $gt, $lt, $in)

Example Query:

db.orders.find({
  status: "completed",
  customerId: "A123",
  total: { $gt: 100 }
}).sort({ createdAt: -1 })

Best Index:

{ status: 1, customerId: 1, createdAt: -1, total: 1 }

Order matters! Reverse order → index not used efficiently.

3. Index Intersection (Deprecated in 5.0+)

Warning: MongoDB no longer merges multiple indexes (since v5.0).
You must create a compound index that covers the full query.

Bad (won’t help):

{ status: 1 }
{ createdAt: 1 }

Good:

{ status: 1, createdAt: -1 }

4. Index Prefix Reuse

MongoDB can reuse prefixes of compound indexes.

Index:

{ a: 1, b: 1, c: 1 }

Can be used for:
- { a: 1 }
- { a: 1, b: 1 }
- { a: 1, b: 1, c: 1 }

Cannot be used for:
- { b: 1 } or { c: 1 } alone

Strategy: Put most selective / frequently filtered fields first.

5. Common Indexing Patterns

A. Equality + Sort

db.logs.find({ level: "ERROR" }).sort({ timestamp: -1 })

Index:

{ level: 1, timestamp: -1 }

B. Range + Sort

db.users.find({ age: { $gte: 18, $lte: 65 } }).sort({ name: 1 })

Index:

{ age: 1, name: 1 }

C. Covered Query (Index-Only)

Return data from index only → no document fetch.

Query:

db.users.find({ status: "active" }, { email: 1, _id: 0 })

Index:

{ status: 1, email: 1 }

Result: Covered query → super fast.

D. Text Search

db.articles.find({ $text: { $search: "mongodb tutorial" } })

Index:

{ title: "text", content: "text" }

Only one text index per collection.

E. Geospatial

db.stores.find({ location: { $near: [40.7, -73.9] } })

Index:

{ location: "2dsphere" }

6. Advanced Index Types

Partial Index

Index only specific documents.

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

Saves space, faster inserts.

Sparse Index

Index only docs with the field.

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

Skips docs without phone.

TTL Index (Auto-Expire)

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

Docs auto-delete after 1 hour.

7. How to Choose Index Fields

Use explain("executionStats") to verify:

db.collection.find(...).explain("executionStats")

Look for:
- "stage": "IXSCAN" → using index
- "totalDocsExamined" ≈ "totalKeysExamined" → efficient
- "usedDisk" → too much data

8. Anti-Patterns (Avoid!)

9. Monitoring & Maintenance

Check Index Usage

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

Shows:
- accesses.ops → how often used
- accesses.since → since last restart

Drop unused indexes:

db.collection.dropIndex("old_index_name")

Current Index List

db.collection.getIndexes()

Rebuild Indexes (if fragmented)

db.collection.reIndex()

10. Real-World Example: E-Commerce Dashboard

Query:

db.orders.find({
  status: "shipped",
  createdAt: { $gte: ISODate("2025-01-01") },
  customerId: { $in: premiumCustomers }
})
.sort({ createdAt: -1 })
.hint(...) // optional

Optimal Index:

{
  status: 1,
  customerId: 1,
  createdAt: -1
}

Why?
- status → equality
- customerId → $in (equality-like)
- createdAt → range + sort

Tools to Help

Summary: Best Practices Checklist

Resources

• MongoDB Indexing Docs
• Index Strategies Guide
• Query Optimization

Pro Tip:

"Index for your queries, not your data."
Analyze real queries using explain() and build indexes accordingly.

Let me know your specific query or schema, and I’ll design the perfect index strategy for you!