MongoDB Indexing and Performance

MongoDB uses B-Tree indexes (and specialized structures for text/geospatial). Without the right indexes, queries do a COLLSCAN (full collection scan) — fine for small collections, catastrophic at scale.

Creating Indexes

// Single field index
db.users.createIndex({ email: 1 })  // 1 = ascending, -1 = descending

// Compound index
db.orders.createIndex({ userId: 1, createdAt: -1 })

// Unique index
db.users.createIndex({ email: 1 }, { unique: true })

// Sparse index (only indexes documents that have the field)
db.users.createIndex({ phone: 1 }, { sparse: true })

// TTL index (auto-delete documents after N seconds)
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

// Text index for full-text search
db.articles.createIndex({ title: 'text', body: 'text' })

// Wildcard index (index all fields in a subdocument)
db.products.createIndex({ 'attributes.$**': 1 })

Diagnosing with explain()

db.orders.find({ userId: 'u123', status: 'PENDING' }).explain('executionStats')

// Key fields to check:
// winningPlan.stage: 'IXSCAN' (good) vs 'COLLSCAN' (bad)
// totalDocsExamined: should be close to nReturned
// executionTimeMillis: actual query time
// keysExamined: how many index keys scanned

Compound Index Prefix Rule

Just like SQL, MongoDB compound indexes follow the prefix rule:

db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

// Index can support:
db.orders.find({ userId: 'u1' })                         // ✅ prefix
db.orders.find({ userId: 'u1', status: 'PENDING' })      // ✅ prefix
db.orders.find({ userId: 'u1', status: 'PENDING', createdAt: { $gt: ... } }) // ✅ full
db.orders.find({ status: 'PENDING' })                    // ❌ not a prefix

Covered Queries — the fastest kind

A covered query is satisfied entirely by the index — MongoDB never reads the actual document.

// Index: { userId: 1, status: 1 }
// Query projects only indexed fields → covered!
db.orders.find(
    { userId: 'u1' },
    { userId: 1, status: 1, _id: 0 }  // _id: 0 to exclude non-indexed _id
).explain() // stage: PROJECTION_COVERED

Index Hints and Forcing Index Usage

// Force MongoDB to use a specific index
db.orders.find({ userId: 'u1' }).hint({ userId: 1, status: 1 })

// Force collection scan (useful for testing)
db.orders.find({ userId: 'u1' }).hint({ $natural: 1 })

Common Performance Patterns

Index selectivity: Index high-cardinality fields (userId, email) before low-cardinality (status, boolean)
ESR Rule for compound indexes: Equality fields first, then Sort fields, then Range fields
Avoid index on small collections: MongoDB may choose COLLSCAN for < ~100 docs anyway
$regex performance: Only uses index if regex is anchored at start: /^abc/

Interview Tips

Explain the difference between IXSCAN (index scan) and COLLSCAN (collection scan) and when each is used.
Know that MongoDB automatically creates an index on _id.
Covered queries avoid fetching documents from disk — a key optimization for read-heavy workloads.

MongoDB Indexing and Performance

Creating Indexes

// Single field index
db.users.createIndex({ email: 1 })  // 1 = ascending, -1 = descending

// Compound index
db.orders.createIndex({ userId: 1, createdAt: -1 })

// Unique index
db.users.createIndex({ email: 1 }, { unique: true })

// Sparse index (only indexes documents that have the field)
db.users.createIndex({ phone: 1 }, { sparse: true })

// TTL index (auto-delete documents after N seconds)
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

// Text index for full-text search
db.articles.createIndex({ title: 'text', body: 'text' })

// Wildcard index (index all fields in a subdocument)
db.products.createIndex({ 'attributes.$**': 1 })

Diagnosing with explain()

db.orders.find({ userId: 'u123', status: 'PENDING' }).explain('executionStats')

// Key fields to check:
// winningPlan.stage: 'IXSCAN' (good) vs 'COLLSCAN' (bad)
// totalDocsExamined: should be close to nReturned
// executionTimeMillis: actual query time
// keysExamined: how many index keys scanned

Compound Index Prefix Rule

Just like SQL, MongoDB compound indexes follow the prefix rule:

db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

// Index can support:
db.orders.find({ userId: 'u1' })                         // ✅ prefix
db.orders.find({ userId: 'u1', status: 'PENDING' })      // ✅ prefix
db.orders.find({ userId: 'u1', status: 'PENDING', createdAt: { $gt: ... } }) // ✅ full
db.orders.find({ status: 'PENDING' })                    // ❌ not a prefix

Covered Queries — the fastest kind

A covered query is satisfied entirely by the index — MongoDB never reads the actual document.

// Index: { userId: 1, status: 1 }
// Query projects only indexed fields → covered!
db.orders.find(
    { userId: 'u1' },
    { userId: 1, status: 1, _id: 0 }  // _id: 0 to exclude non-indexed _id
).explain() // stage: PROJECTION_COVERED

Index Hints and Forcing Index Usage

// Force MongoDB to use a specific index
db.orders.find({ userId: 'u1' }).hint({ userId: 1, status: 1 })

// Force collection scan (useful for testing)
db.orders.find({ userId: 'u1' }).hint({ $natural: 1 })

Common Performance Patterns

Index selectivity: Index high-cardinality fields (userId, email) before low-cardinality (status, boolean)
ESR Rule for compound indexes: Equality fields first, then Sort fields, then Range fields
Avoid index on small collections: MongoDB may choose COLLSCAN for < ~100 docs anyway
$regex performance: Only uses index if regex is anchored at start: /^abc/

Interview Tips

Explain the difference between IXSCAN (index scan) and COLLSCAN (collection scan) and when each is used.
Know that MongoDB automatically creates an index on _id.
Covered queries avoid fetching documents from disk — a key optimization for read-heavy workloads.