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

Without Index	With Index
Full collection scan (reads every doc)	Direct access via B-tree
O(n) time	O(log n) time
High latency, CPU, I/O	Fast, scalable

---

1. Types of Indexes in MongoDB

Index Type	Use Case	Example
Single Field	Sort or filter on one field	{ age: 1 }
Compound	Multiple fields (order matters!)	{ status: 1, createdAt: -1 }
Multikey	Arrays	{ tags: 1 }
Text	Full-text search	{ content: "text" }
Geospatial	Location queries	{ location: "2dsphere" }
Hashed	Sharding, equality matches	{ userId: "hashed" }
TTL	Auto-expire docs	{ createdAt: 1 } with expireAfterSeconds
Partial	Index subset of docs	Only index active users
Sparse	Index only docs with field	Skip missing fields
Unique	Enforce uniqueness	{ email: 1 } with unique: true

---

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 }

Field	Role
status	Equality
customerId	Equality
createdAt	Sort
total	Range

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

Priority	Field Type
1	Equality filters (=)
2	Sort fields
3	Range filters (>, <, $in)
4	Projection fields (for covered queries)

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!)

Bad Practice	Why
Too many indexes	Slows inserts, uses RAM/disk
Indexing low-cardinality fields first	Poor selectivity
Indexing every field	Wastes resources
Forgetting to drop unused indexes	Maintenance overhead

---

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

Tool	Purpose
MongoDB Compass	Visual index manager
Atlas Performance Advisor	Auto-suggests indexes
explain()	Query plan analysis
$indexStats	Usage tracking

---

Summary: Best Practices Checklist

Practice	Done?
Use compound indexes for multi-field queries	Yes
Follow ESR rule: Equality → Sort → Range	Yes
Use covered queries when possible	Yes
Use partial/sparse to reduce size	Yes
Monitor with $indexStats	Yes
Drop unused indexes	Yes
Avoid over-indexing	Yes

---

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!