An index is a B-tree (or specialised structure) on one or more fields that lets MongoDB find documents without scanning the whole collection. Indexes turn a 60-second query into a 5-millisecond one — and turn an undertuned cluster into a poorly-running mess if used wrong (too many, too wide, wrong order). This post covers every index type, the ESR rule (Equality, Sort, Range) for compound-index ordering, partial / sparse / TTL / wildcard / hashed variants, index intersection, covered queries, and the build options (background, unique, collation).

Series map: 00 Foundations → 01 CRUD → 02 Query Operators → 03 Indexes (this post) → 04 Query Optimization → 05 Aggregation Basics → … → 17 Drivers & Case Studies.

Learning Outcome

  • Create single-field, compound, multikey, text, geospatial, hashed indexes.
  • Apply the ESR rule to compound index ordering.
  • Use partial, sparse, TTL, wildcard, unique indexes correctly.
  • Recognise covered queries.
  • Understand index intersection and when not to rely on it.
  • List, hint, drop, hide indexes.
  • Build indexes in production without downtime.

Chapter 1 — Index basics

Every MongoDB collection has at least one index — on _id (unique, auto-created, can't drop). All others are user-created.

1
2
3
4
5
db.users.createIndex({ email: 1 })          // ascending B-tree
db.users.createIndex({ created_at: -1 })     // descending
db.users.getIndexes()                        // list
db.users.dropIndex("email_1")                // by name

Naming: auto-generated as <field>_<dir> joined by _. Use { name: "my_idx" } option for custom.

Cost trade-off

  • Indexes speed reads that filter / sort / project on indexed fields.
  • Indexes slow writes — every insert / update / delete must update every relevant index.
  • Indexes use RAM — frequently accessed parts of indexes must fit in the WiredTiger cache for speed (Post 15).
  • Indexes use disk — typically 10-30% of collection data size each.

Rule: index for the queries you actually run, drop indexes never used (Post 04 covers $indexStats to find unused).

Chapter 2 — Single-field index

The simplest:

1
2
3
4
5
db.users.createIndex({ email: 1 })
db.users.find({ email: "ada@x" })           // uses the index — O(log n)
db.users.find({ email: /^ad/ })             // uses the index (left-anchored regex)
db.users.find({ email: /ad/ })              // can't use the index (no left anchor)

Direction (1 vs -1) doesn't matter for a single-field index — the B-tree can be walked either way. It matters for compound indexes when sorting on multiple fields.

Chapter 3 — Compound index + the ESR rule

A compound index is on multiple fields:

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

The order of fields in the index matters enormously. The ESR rule:

E — Equality fields first.
S — Sort fields second.
R — Range fields last.

Why: B-trees can serve range scans only on the trailing indexed prefix once equality fields have been matched. Out-of-order indexes do partial work then full scans.

Example

Query: "Most recent paid orders for user 1, sorted desc by date."

1
2
db.orders.find({ user_id: 1, status: "paid" }).sort({ at: -1 })

Best index:

1
2
3
db.orders.createIndex({ user_id: 1, status: 1, at: -1 })
//                       ^^^^^^^^^^^ equality first ^^^^ sort/range last

The index serves:

  • find({ user_id: 1 }) — uses prefix.
  • find({ user_id: 1, status: "paid" }) — uses prefix.
  • find({ user_id: 1 }).sort({ status: 1 }) — status comes right after the equality field, so the index order is already the sort order.
  • find({ user_id: 1, status: "paid", at: { $gte: D } }).sort({ at: -1 }) — full use.

Doesn't serve:

  • find({ status: "paid" }) — user_id is the prefix; can't skip it.
  • find({ user_id: 1 }).sort({ at: -1 }) — the sort. status sits between user_id and at and is not in the filter, so the index finds the rows but MongoDB must still sort them in memory. Add status to the filter, or create { user_id: 1, at: -1 }.

Prefix rule

A compound index { a: 1, b: 1, c: 1 } can serve:

  • { a }
  • { a, b }
  • { a, b, c }

But not:

  • { b } alone — a is the required prefix.

Trick: if you frequently query by b alone, you need a separate index { b: 1 }. Don't try to make one mega-index do everything.

The ESR rule: a query mapped onto a compound index as equality, equality, sort, with example keys and the prefix rule

Direction matters for sort

Index { at: 1, user_id: 1 }:

  • .sort({ at: 1, user_id: 1 }) — uses it directly.
  • .sort({ at: -1, user_id: -1 }) — uses it in reverse scan (also OK).
  • .sort({ at: 1, user_id: -1 }) — can't use it (mixed direction).

Build the index to match your most common sort direction.

Chapter 4 — Multikey index (arrays)

When indexed field is an array, MongoDB creates one index entry per array element. This is called a multikey index — automatic, no special syntax.

1
2
3
4
db.users.createIndex({ tags: 1 })
db.users.find({ tags: "math" })             // matches if "math" in array
db.users.find({ tags: { $all: ["math", "eng"] } })   // can use the index

Limits:

  • Only one array field in a compound multikey index. { tags: 1, scores: 1 } where both are arrays is forbidden (would explode entries quadratically).
  • Multikey indexes are larger than single-value indexes.

For arrays of sub-documents, use full-path field names:

1
2
db.orders.createIndex({ "items.p": 1 })      // multikey on items[].p

Chapter 5 — Text index

1
2
3
db.articles.createIndex({ title: "text", body: "text" })
db.articles.createIndex({ "$**": "text" })       // every string field — careful, big

One text index per collection. Use only when "real" search (Atlas Search, Lucene, ES) isn't available. Coverage of operators in Post 02 Chapter 6.

Chapter 6 — Geospatial index

1
2
db.places.createIndex({ location: "2dsphere" })

Required for $near, $geoWithin, $geoIntersects on GeoJSON points/polygons. Post 02 Chapter 8.

Chapter 7 — Hashed index

1
2
db.users.createIndex({ _id: "hashed" })

Used for:

  • Sharding with a hashed shard key — distributes writes evenly (Post 11).
  • Equality lookups on fields where you don't need range queries.

Doesn't support range queries ($gt, etc.). Don't combine with sort on the hashed field.

Chapter 8 — Partial index — index only some documents

1
2
3
4
5
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: true } }
)

The index contains only docs where active: true. Queries that include active: true can use it; others can't.

Use cases:

  • Index only "open" tickets / "active" users / non-deleted records — smaller index, faster builds.
  • Conditional uniqueness (next).

Chapter 9 — Sparse index — index only docs that have the field

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

Skips documents missing the field entirely. Smaller index when the field is optional.

Partial vs sparse:

  • Sparse — only checks field presence.
  • Partial — checks arbitrary partialFilterExpression.

Partial is the modern superset. New work: use partial.

Chapter 10 — Unique index

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

Attempts to insert a duplicate raise E11000 duplicate key error.

Unique + partial — conditional uniqueness

1
2
3
4
5
6
// emails must be unique among active users only
db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { active: true } }
)

Common pattern: soft-deleted users may share an email with a new active user.

Unique + multikey

{ tags: 1 } unique applies per array element across documents — usually not what you want. Use a separate dedicated collection for tag uniqueness.

Chapter 11 — TTL index — auto-delete after N seconds

1
2
3
4
5
db.sessions.createIndex(
  { last_active_at: 1 },
  { expireAfterSeconds: 3600 }     // expire 1 hour after last_active_at
)

A background thread deletes documents whose indexed date is older than expireAfterSeconds ago. Runs roughly every 60 s — TTL is approximate, not exact.

Field must be a Date (or array of Dates → uses earliest).

Common uses:

  • Sessions, OTP codes, password-reset tokens.
  • Short-term caches.
  • Rate-limit counters.

To expire at a specific instant: store the desired expiry timestamp directly, set expireAfterSeconds: 0:

1
2
db.coupons.createIndex({ expires_at: 1 }, { expireAfterSeconds: 0 })

Watch out: TTL deletes generate write load + replication traffic — a large TTL cleanup spike can saturate a small instance.

Chapter 12 — Wildcard index — index unknown fields

1
2
3
4
db.docs.createIndex({ "$**": 1 })                          // every field
db.docs.createIndex({ "meta.$**": 1 })                     // every subfield of meta
db.docs.createIndex({ "$**": 1 }, { wildcardProjection: { name: 0 } })  // exclude

Useful when documents have heterogeneous / unknown fields and the app queries arbitrary attributes — common in multi-tenant SaaS where each tenant has custom fields.

Costs: big indexes, expensive writes. Reach for only when access patterns truly vary.

Chapter 13 — Covered queries

A query is covered when:

  1. Every queried field is in an index.
  2. Every projected field is in the same index.
  3. Query doesn't return _id unless _id is in the index.

Covered queries return results straight from the index — no document fetch — extremely fast.

1
2
3
4
5
6
7
8
db.users.createIndex({ email: 1, name: 1 })
 
db.users.find(
  { email: "ada@x" },
  { email: 1, name: 1, _id: 0 }    // no _id, only indexed fields
)
// covered query — no document fetch

explain() (Post 04) shows IXSCAN with no following FETCH stage when a query is covered.

Chapter 14 — Index intersection (use sparingly)

MongoDB can intersect two single-field indexes to satisfy a multi-field query, but it's usually slower than a single compound index that matches the query.

1
2
3
4
5
6
7
8
// Two indexes
db.orders.createIndex({ user_id: 1 })
db.orders.createIndex({ status: 1 })
 
// Query
db.orders.find({ user_id: 1, status: "paid" })
// May use intersection — typically slower than a compound { user_id: 1, status: 1 }

Rule: design compound indexes for the queries you run, not single-field per-column indexes.

Chapter 15 — Build options

Build in background (5.0+: always)

Index builds since 5.0 use a hybrid foreground/background protocol that doesn't block writes. No special flag needed. (Older versions used { background: true } — still accepted, no-op.)

Collation — case + locale-aware

1
2
3
4
5
6
7
db.users.createIndex(
  { name: 1 },
  { collation: { locale: "en", strength: 2 } }     // case-insensitive
)
 
db.users.find({ name: "ada" }).collation({ locale: "en", strength: 2 })

Without matching collation on the query, the index is not used.

Hiding (test removal without dropping)

1
2
3
4
db.users.hideIndex("email_1")            // index still maintained but not used by planner
db.users.unhideIndex("email_1")
db.users.dropIndex("email_1")            // permanent

Safe pattern for "is this index needed?": hide for a week, observe query performance, drop if no regression.

Chapter 16 — Production rules of thumb

  1. Index every field used in a frequent filter, sort, or join. Not more.
  2. Compound indexes follow ESR.
  3. Sort direction in the index matches your most common sort.
  4. Use partial indexes to shrink indexes for filtered subsets (active users, open tickets).
  5. Use TTL for inherently temporary data.
  6. Index size + frequently-accessed entries should fit in RAM — Post 15.
  7. Drop unused indexes — $indexStats shows usage (Post 04).
  8. Build new indexes in off-peak windows for large collections, even with hybrid background builds — backfill is I/O heavy.
  9. Don't index every field "just in case" — write amplification + RAM waste.
  10. Test with explain() before shipping any new query — Post 04.

Practice Exercises

Exercise 1 — Create + verify

In the shop dataset, create an index on users.email and confirm with getIndexes().

Exercise 2 — ESR design

Given query find({ user_id, status: "paid" }).sort({ at: -1 }), design the compound index. Explain why each field is in its position.

Answer

{ user_id: 1, status: 1, at: -1 }. user_id and status are equality → first two. at is sort + range candidate → last. Direction -1 matches the desc sort.

Exercise 3 — Partial unique

Create an index that ensures email is unique among active users only.

Answer 
1
2
3
4
5
db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { active: true } }
)

Exercise 4 — TTL for sessions

Create a sessions collection where each document has a created_at Date and auto-expires after 1 hour.

Exercise 5 — Covered query

Create an index { email: 1, name: 1 }. Write a query that's covered (no document fetch). Use .explain('executionStats') to confirm totalDocsExamined: 0.

Exercise 6 — Hide-then-drop

Pick any non-essential index. Hide it. Wait a day. Inspect query performance + $indexStats. Drop it.

Cheat-sheet

What How
Create db.c.createIndex({ f: 1 })
List db.c.getIndexes()
Drop db.c.dropIndex("name_1")
Stats db.c.aggregate([{ $indexStats: {} }])
Compound { a: 1, b: 1, c: -1 }
ESR rule Equality → Sort → Range
Prefix rule compound {a,b,c} serves {a}, {a,b}, {a,b,c}
Multikey automatic on arrays; one array field per compound
Unique { unique: true }
Partial { partialFilterExpression: { active: true } }
Sparse { sparse: true } (legacy; use partial)
TTL { expireAfterSeconds: N } on Date field
Wildcard { "$**": 1 } for unknown fields
Hashed { _id: "hashed" } for sharding equality
Text { field: "text" } — one per collection
Geo { location: "2dsphere" }
Collation { collation: { locale, strength } }
Hide db.c.hideIndex("name_1")
Covered query all fields in one index; no document fetch

What's next

Post 04 covers query optimization — the explain('executionStats') output (IXSCAN vs COLLSCAN, totalKeysExamined vs nReturned, executionTimeMillis), the planner, hint(), plan cache, $indexStats, and the diagnostic flow for any "why is this query slow?".

References

Note: this post is in progress. Planned additions: 2dsphere index quirks, clustered collections (5.3+), index build progress monitoring, rolling-index-build pattern in replica sets.