MongoDB — Indexes

Indexes turn a collection scan into a B-tree lookup. Same idea as SQL but with quirks: arrays index into multikey indexes, embedded documents need dotted paths, and the left-prefix rule applies but in a slightly different way.

Index types

Left-prefix rule (compound)

db.orders.createIndex({ user_id: 1, status: 1, created_at: -1 })

This index helps queries that filter by:

• user_id alone
• user_id + status
• user_id + status + created_at

But not queries that skip the prefix:

• status alone → full scan
• created_at alone → full scan

Order matters. Put high-cardinality and equality-filtered fields first.

Compound index for filter + sort

// Query
db.orders.find({ user_id: 7, status: "pending" }).sort({ created_at: -1 })

// Index
db.orders.createIndex({ user_id: 1, status: 1, created_at: -1 })

The sort direction in the index matches the sort in the query. If you sort descending, the index entry for that field is descending. Otherwise MongoDB has to scan.

Rule (ESR): Equality first, Sort second, Range last.

// Query: equality on user_id, sort by created_at, range on total
db.orders.find({ user_id: 7, total: { $gt: 100 } }).sort({ created_at: -1 })

// Index following ESR
db.orders.createIndex({ user_id: 1, created_at: -1, total: 1 })

Multikey indexes (arrays)

{ "_id": ..., "tags": ["urgent", "vip", "review"] }
db.things.createIndex({ tags: 1 })

// Now find by any tag is indexed
db.things.find({ tags: "urgent" })

Each array element creates an index entry. A doc with 10 tags creates 10 entries.

Restrictions:

• A compound index can contain at most one multikey field. { tags: 1, categories: 1 } where both are arrays → error.
• Multikey indexes can’t be hashed.
• $or with multikey can produce poor plans.

Index on embedded documents

{ "_id": ..., "address": { "city": "Berlin", "zip": "10115" } }

// Index just the city
db.users.createIndex({ "address.city": 1 })

Dotted paths into the embedded structure. Same B-tree, just nested key naming.

Partial indexes

db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: true }, unique: true }
)

Indexes only docs matching the filter. Smaller index, faster builds, but query must include the filter to use the index.

Use for: “unique email among active users,” “TTL only on session docs that aren’t pinned,” etc.

TTL indexes

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

Background process scans every ~60s and deletes expired docs. Eventual deletion — not immediate at exactly N seconds.

For per-doc TTL, set expireAfterSeconds: 0 and write the absolute expiry time to the indexed field:

{ "_id": ..., "expires_at": ISODate("2026-05-13T18:00:00Z") }
db.sessions.createIndex({ expires_at: 1 }, { expireAfterSeconds: 0 })

Index intersection vs single compound

MongoDB can use two single-field indexes via intersection:

db.orders.createIndex({ user_id: 1 })
db.orders.createIndex({ status: 1 })

db.orders.find({ user_id: 7, status: "pending" })
// May use intersection of the two indexes

But compound index is almost always faster than intersection. Plan compound indexes for your common queries; don’t rely on intersection.

Covered queries

If a query’s projection only requests indexed fields, MongoDB returns the answer from the index without touching the document — “covered query.”

db.users.createIndex({ email: 1, name: 1 })
db.users.find({ email: "a@b.com" }, { _id: 0, name: 1 })   // covered: index has email + name

Big speedup; documents stay in storage. Note: must exclude _id (or include it in the index) since _id is returned by default.

Index size and write cost

Each index adds:

• Disk space (B-tree).
• Write overhead: every insert / update updates every relevant index.
• Memory pressure (WiredTiger caches index pages).

Rule of thumb: 5-10% write overhead per index. Don’t add an index “in case.” Add for measured queries.

// Check current indexes
db.orders.getIndexes()

// Check index size
db.orders.stats().indexSizes

explain()

db.orders.find({ user_id: 7 }).explain("executionStats")

Key fields:

• winningPlan.stage — IXSCAN (good) or COLLSCAN (bad).
• executionStats.totalDocsExamined — should be close to nReturned.
• executionStats.executionTimeMillis.
• executionStats.totalKeysExamined — index entries scanned.

Ratio totalDocsExamined / nReturned >> 1 means an index miss or wrong index choice.

Wildcard indexes

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

Indexes every field in every document. Tempting; usually a mistake:

• Huge index size.
• Write overhead per field.
• Doesn’t actually help every query — only single-field queries match.

Use a wildcard index only for ad-hoc query patterns with no fixed schema; otherwise plan specific indexes.

Index build modes

• Foreground (legacy): blocks the DB. Don’t use in production.
• Background (legacy): default since 2.6 in many drivers; deprecated in MongoDB 5.0+.
• background: true is the default and you usually don’t think about it.

For very large collections (100M+ docs), index builds take hours; use the rolling build via secondary promotion if HA matters.

Common bugs

• Sort can’t use index. Sort direction mismatch with compound index — MongoDB scans and sorts in memory (slow, may fail at 32 MB sort limit).
• $ne, $nin, $not don’t use indexes efficiently (have to scan to confirm “not equal”).
• $regex without anchor doesn’t use index. ^foo is sargable; foo isn’t.
• Sort + pagination with deep offsets is slow even with index. Use range-based pagination ({ _id: { $gt: lastId } }).
• Two array fields in compound index → error.
• Index too big to fit in RAM → slow as it pages from disk.

Interview angle

• “Design a compound index for db.orders.find({user_id: 7, status: 'pending'}).sort({created_at: -1}).” — { user_id: 1, status: 1, created_at: -1 }. ESR: Equality on user_id + status, then Sort on created_at descending matching query sort direction.
• “What’s a multikey index?” — automatic compound-index behavior when a field is an array. Each element creates an entry. One multikey field per compound index allowed.
• “What’s a covered query?” — when the projection only requests fields present in the index, MongoDB answers from the index without reading documents. Major speedup. Must exclude _id if not in the index.
• “How does index intersection compare to compound?” — intersection uses two single-field indexes for AND queries. Compound is almost always faster; design compound for known queries; don’t rely on intersection.
• “What’s the ESR rule?” — Equality, Sort, Range order in a compound index. Equality fields use the index efficiently; sort gets ordered access; range scans are last because they bound the index traversal.
• “How do TTL indexes work?” — index on a date field with expireAfterSeconds. Background process deletes expired docs every ~60s. Eventual deletion; not exactly at the threshold. For per-doc TTL, store absolute expiry time and use expireAfterSeconds: 0.