MongoDB Indexing Cheat Sheet

1. Why Indexes Matter

• Speed up queries by avoiding full collection scans
• Improve sort performance
• Reduce latency as datasets grow
• Increase read efficiency at the cost of extra write overhead and storage

2. Creating Indexes

Basic Syntax

db.collection.createIndex({ field: 1 })        // ascending
db.collection.createIndex({ field: -1 })       // descending
db.collection.createIndex({ a: 1, b: -1 })     // compound

Useful Options

db.collection.createIndex({ email: 1 }, { unique: true })
db.collection.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 }) // TTL
db.collection.createIndex({
  status: 1
}, {
  partialFilterExpression: { status: { $exists: true } }
})

3. Index Types

Single‑Field Index

• Best for equality and range queries on one field
• Small and efficient

Compound Index

• Order matters: { a: 1, b: 1 } supports queries on a and a + b
• Equality fields first, range fields last

Multikey Index

• Automatically created when indexing arrays
• Supports queries on array elements

Text Index

db.posts.createIndex({ title: "text", body: "text" })

• Supports $text search
• Only one text index per collection

Hashed Index

db.users.createIndex({ userId: "hashed" })

• Ideal for sharding and uniform distribution
• Only supports equality queries

TTL Index

• Automatically deletes documents after a time period
• Field must contain a date

4. Query Patterns Indexes Support

Equality

db.users.find({ email: "[email protected]" })

Range

db.orders.find({ total: { $gt: 100 } })

Sort

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

Combined Filter + Sort

Index must match both filter and sort order. Example:

{ status: 1, createdAt: -1 }

5. How to Read explain()

db.collection.find({ a: 5 }).explain("executionStats")

Key Metrics

• winningPlan → which index was used
• nReturned → number of documents returned
• totalDocsExamined → should be low
• totalKeysExamined → should be low
• stage should ideally be IXSCAN or FETCH, not COLLSCAN
• query targeting (=totalDocsExamined / nReturned) should be as close to 1 as possible. Any higher value indicates the server is filtering documents out after retrieval due to a lack of a usable index. This may not be an issue for occasional queries, but can cause memory cache issues at scale.

6. Best Practices

• Index fields used in filters and sorts
• Avoid indexing low‑cardinality fields
• Keep indexes narrow and small
• Use compound indexes for common query shapes
• Drop unused indexes to reduce write overhead
• Avoid unbounded array growth on multikey indexes
• Monitor index size to ensure it fits in RAM

7. Common Pitfalls

• Over‑indexing slows writes
• Wrong index order in compound indexes
• Using $or without supporting indexes
• Sorting without an index causes in‑memory sort
• Large documents with many indexed fields increase storage cost

8. Quick Reference Table