Filter concepts by levelShowing all levels.

MongoDB · Section 10

Indexing Fundamentals

Level
intermediate
Read
40 min
Concepts
9

Why indexes exist and how they turn a full collection scan into a targeted lookup; the index shapes — single-field, compound, multikey, unique, sparse/partial, TTL, and text — with their real syntax, behavior, and trade-offs; and the equality-sort-range key-ordering principle that decides whether a compound index actually serves a query's filter, sort, and range together.

What is true here

  1. No index means MongoDB checks every document (COLLSCAN); an index lets it jump to matches (IXSCAN) — verify with explain().
  2. A compound index serves queries matching its leading field(s), in the order the index defines — not any field alone.
  3. Indexing an array field automatically makes the index multikey, with one entry per array element.
  4. A unique index treats a missing field as null, so only one document may omit it unless the index is also sparse or partial.
  5. Ordering a compound index equality, then sort, then range (ESR) lets one index serve all three without forcing an in-memory sort.

What you will be able to do

  • Explain why a query is slow using explain()'s stage name and totalDocsExamined
  • Choose the right index type — single-field, compound, multikey, unique, sparse/partial, TTL, or text — for a given need
  • Predict when a unique index will reject a document due to a missing field
  • Set up a TTL index correctly, and explain why its deletion is not instantaneous
  • Order a compound index's fields to serve equality, sort, and range together

Why and how indexes work

The core mechanism — scan vs. index — then the two everyday index shapes: single-field and compound.

Why indexes exist

corebeginner

Without an index, a query checks every document in the collection — a collection scan. An index is a sorted structure on one or more fields that lets MongoDB jump straight to matching values instead of checking each document in turn.

Think of it as

An index is like a book's index versus reading every page to find a topic: the sorted list of terms with page numbers lets you jump directly to what matters, at the cost of maintaining that list whenever the book (the collection) changes. The same trade applies to every index: faster reads, in exchange for extra work on every write that touches an indexed field.

text
db.<collection>.createIndex({ <field>: 1 })   // 1 = ascending, -1 = descending

What we're doing: Show the same query before and after an index, with explain() confirming the change from COLLSCAN to IXSCAN.

before-and-after-index.txttext
// Before: no index on email — a collection scan checks every document
db.users.find({ email: "a@x.com" }).explain("executionStats")
// -> stage: "COLLSCAN", totalDocsExamined: <every document>

// After: db.users.createIndex({ email: 1 })
db.users.find({ email: "a@x.com" }).explain("executionStats")
// -> stage: "IXSCAN", totalDocsExamined: 1
1
Without an index, MongoDB has no shortcut — it examines every document to check whether its email field matches.
5
After the index exists, the same query jumps directly to the matching entry, examining only the documents that actually match.

Why this works: explain() is what turns "indexes make queries faster" from a claim into something verifiable — the stage name and totalDocsExamined are the concrete, checkable evidence of what actually changed.

Assuming a query is fast because "there's an index on the collection," without checking it is the right index

Wrong

text
// db.users.createIndex({ name: 1 }) exists, but the actual slow query filters on { email: "..." }

Better

text
// Check explain() for the specific query in question — an index on a different field does not help a query that does not use it

What you see: A collection "has indexes" but a specific slow query still shows COLLSCAN in its explain() output.

Why: A collection scan happens per query, based on whether that query's filter fields match an existing index — the mere existence of some index on the collection says nothing about whether it helps a query on different fields.

What an index changes about a query

no index

COLLSCAN — check every document

createIndex

a sorted structure

IXSCAN

jump to matches

  1. no index — COLLSCAN — check every document
  2. createIndex — a sorted structure
  3. IXSCAN — jump to matches

Scan vs. index, at a glance

Scan vs. index, at a glance
PropertyWithout an index (COLLSCAN)With an index (IXSCAN)
Read costgrows with collection sizegrows with matching documents, not collection size
Write costno index maintenanceeach write also updates the index
Sorted outputrequires an in-memory sortcan return already sorted, if the index matches

Together

text
db.users.createIndex({ email: 1 })
db.users.find({ email: "a@x.com" }).explain("executionStats")
// -> stage: "IXSCAN" instead of "COLLSCAN"; totalDocsExamined much smaller than collection size

Remember: No index means a collection scan (COLLSCAN) — every document checked. An index (IXSCAN) lets MongoDB jump to matches, at the cost of extra work on every write.

See also: single field indexes · compound indexes

Single-field indexes

standardbeginner

An index on exactly one field, e.g. createIndex({ email: 1 }). It speeds up queries and sorts on that field, in either direction — ascending vs. descending only matters for a single-field index when combined with another sort.

Think of it as

A single-field index is the simplest case of the same sorted-structure idea that every index uses — one column's values, sorted, with a pointer back to each document. It is the right tool exactly when a field is queried or sorted on by itself, without other fields in the same filter.

text
db.<collection>.createIndex({ <field>: 1 })

What we're doing: Show a single-field index supporting both an equality query and a sort on the same field.

single-field-index-usage.txttext
db.products.createIndex({ price: 1 })

db.products.find({ price: 29.99 })          // equality — uses the index
db.products.find().sort({ price: -1 })      // sort — the same index serves both directions
1
One index, created ascending, is enough to serve both an equality lookup and a sort in either direction on this field.
4
MongoDB can walk a single-field index backward as easily as forward, so the ascending index still serves a descending sort.

Why this works: A single-field index's direction only becomes a real constraint once it is part of a compound index serving a sort on more than one field — see compound index design.

Creating both an ascending and a descending index on the same single field

Wrong

text
db.products.createIndex({ price: 1 })
db.products.createIndex({ price: -1 })  // redundant

Better

text
db.products.createIndex({ price: 1 })  // one index serves sorts in either direction

What you see: Two indexes exist on the same field, doubling the write cost of maintaining them, with no read benefit over having just one.

Why: A single-field index can be walked in either direction by the query planner — the ascending/descending choice only matters when the field participates in a compound index alongside another sorted field.

Remember: createIndex({ field: 1 }) — a single-field index serves equality, range, and sort on that field in either direction; no need for a second index just to reverse the sort order.

See also: why indexes exist · compound indexes

Compound indexes

coreintermediate

An index on more than one field, in a specific order: createIndex({ a: 1, b: 1 }). It supports queries filtering on a, or on a and b together, but generally not on b alone — the field order matters, and gets its own full treatment in section 11.

Think of it as

A compound index sorts documents first by its first field, then by its second field within each value of the first, and so on — like a phone book sorted by last name, then first name. That is why it can find "Smith" quickly but cannot use the same index to jump straight to every "John," regardless of last name.

text
db.<collection>.createIndex({ <field1>: 1, <field2>: -1, ... })

What we're doing: Show one compound index replacing what would otherwise need two single-field indexes for a two-field query.

compound-replaces-two-single.txttext
// Instead of two single-field indexes, each only partly helpful for this query:
// db.orders.createIndex({ customerId: 1 })
// db.orders.createIndex({ status: 1 })

// One compound index serves the combined filter directly:
db.orders.createIndex({ customerId: 1, status: 1 })
db.orders.find({ customerId: 1, status: "pending" })
// -> a single IXSCAN walk, not an intersection of two separate index scans
2
Two separate single-field indexes can each narrow the search, but MongoDB has to intersect their results rather than walking one sorted structure directly to the answer.
5
A compound index on both fields, in the order the query filters on them, answers the same query in one direct walk.

Why this works: A compound index is not just "two indexes combined" — it is one sorted structure over the combination of fields, which is why it can serve a two-field equality query more directly than intersecting two single-field indexes.

Assuming a compound index on { a, b } equally helps a query filtering on b alone

Wrong

text
db.orders.createIndex({ customerId: 1, status: 1 })
db.orders.find({ status: "pending" })  // customerId not in the filter at all

Better

text
// Either reorder the compound index if status-only queries are common, or add a separate index on status

What you see: A query filtering only on the second field of a compound index still shows COLLSCAN or a much larger totalDocsExamined in explain(), despite "having an index that covers that field."

Why: A compound index is one sorted structure ordered by its leading field first — a query that does not constrain the leading field cannot use the index to narrow its starting point, the same reason a phone book sorted by last name cannot jump straight to every "John."

Sorted by customerId first, then createdAt within each customer

customerId: 1

primary sort key

customerId: 1, createdAt: 1

sub-sorted within each customer

  1. customerId: 1 — primary sort key
  2. customerId: 1, createdAt: 1 — sub-sorted within each customer

What a compound index on { a: 1, b: 1 } actually serves

What a compound index on { a: 1, b: 1 } actually serves
Query filters onUses this index?
a onlyyes — a is the leading field
a and byes — both fields, in index order
b onlyno — b alone is not a usable prefix

Together

text
db.orders.createIndex({ customerId: 1, createdAt: -1 })

db.orders.find({ customerId: 1 })                                  // uses the index
db.orders.find({ customerId: 1, createdAt: { $gte: someDate } })   // uses the index
db.orders.find({ createdAt: { $gte: someDate } })                  // does NOT use it — customerId not given

Remember: createIndex({ a: 1, b: 1 }) sorts by a, then b within each a — serves queries on a alone or a+b, generally not b alone. Field order must match real query patterns.

See also: single field indexes · esr index reasoning · compound index interaction

Advertisement

Specialized index types

Arrays, uniqueness, shrinking an index by filter, auto-expiring documents, and basic word search — each with its own real behavior and trade-offs.

Multikey indexes

standardintermediate

When you index a field that holds an array, MongoDB automatically creates one index entry per array element instead of one per document — this is called a multikey index, and it happens without any special syntax.

Think of it as

A normal index has one entry per document. A multikey index breaks that one-to-one mapping: a document with a 5-element array gets 5 entries in the index, all pointing back to the same document. That is what lets a query like { tags: "sale" } find a document whose tags array contains "sale" among several other values, without scanning the whole array by hand.

text
db.<collection>.createIndex({ <arrayField>: 1 })   // automatically multikey if the field holds arrays

What we're doing: Show a multikey index matching a document via one element among several in an array.

multikey-basic.txttext
db.products.createIndex({ tags: 1 })

db.products.insertOne({ _id: 1, tags: ["electronics", "sale", "clearance"] })
db.products.find({ tags: "sale" })   // matches — "sale" is one of the array's elements
1
No special syntax marks this as multikey — MongoDB detects the array shape automatically when the field is indexed.
4
The index has a separate entry for each of the three tag values, all pointing at document _id 1 — the query matches on any one of them.

Why this works: Automatic multikey behavior is what lets "does this array contain X" queries use an index at all — without it, every array-field query would need a full collection scan checking each document's array by hand.

Not realizing an indexed field became multikey after the schema changed to store an array

Wrong

text
// A field started as a scalar, later became an array in some documents — the existing index silently becomes multikey

Better

text
// Check whether a field is ever an array before combining it into a compound index — multikey compound indexes have restrictions (section 12)

What you see: A compound index that worked fine while a field was scalar-only starts hitting the "compound multikey" restriction once some documents store an array there instead.

Why: MongoDB decides multikey-ness from the actual data, not from a declared schema — a field that is sometimes scalar and sometimes an array can silently turn an index multikey the first time a document with an array is inserted.

Remember: Indexing an array field automatically makes the index multikey — one entry per array element, matching if any element matches the query. No special syntax; MongoDB detects it from the data.

See also: compound indexes · array operators

Unique indexes

coreintermediate

createIndex({ field: 1 }, { unique: true }) rejects any insert or update that would create a second document with the same value in that field. A missing field counts as null — and only one document is allowed to have a missing/null value, the same as any other duplicate.

Think of it as

A unique index enforces "no two documents agree on this value" at the database level, the same guarantee a relational UNIQUE constraint gives — but it is opt-in and per-index, not implied by any field simply existing. The null/missing behavior surprises people: MongoDB treats "the field is absent" as a value (null) for uniqueness purposes, so it counts toward the same one-document limit as any other value.

text
db.<collection>.createIndex({ <field>: 1 }, { unique: true })

What we're doing: Show the missing-field-counts-as-null behavior concretely, including the exact error.

unique-null-behavior.txttext
db.users.createIndex({ email: 1 }, { unique: true })

db.users.insertOne({ name: "Arya Stark" })     // succeeds — email absent, treated as null
db.users.insertOne({ name: "Jon Snow" })       // fails — E11000 duplicate key error on null
1
The index is unique on "email" — nothing here suggests it would ever reject documents that never set email at all.
3
The first document with a missing email succeeds; the second fails, because MongoDB treats the missing field as the value null on both, and null can only appear once under a unique constraint.

Why this works: This is a common surprise precisely because "the field is missing" does not feel like "the field has a value" — but for a unique index, MongoDB has to store something for every document, and null is what an absent field becomes.

Assuming a unique index on an optional field allows unlimited documents to omit it

Wrong

text
// "email is optional, so a unique index on it should be fine for many users without one" — untested assumption

Better

text
// Combine unique: true with sparse: true (or a partialFilterExpression) if the field is genuinely optional for many documents

What you see: The second user who signs up without an email address gets a confusing duplicate-key error that has nothing to do with an actual email collision.

Why: A plain unique index does not distinguish "optional and absent" from "present with a real duplicate value" — sparse or partial unique indexes exist specifically to exclude missing-field documents from the uniqueness check, and section 13 covers exactly this combination.

Missing and null share one uniqueness slot

unique index on "email"

one document

email missing → treated as null

a second one

also missing → rejected, duplicate null

  • unique index on "email"
    • one document — email missing → treated as null
    • a second one — also missing → rejected, duplicate null

Unique index behavior by field state

Unique index behavior by field state
Field stateCounts toward uniqueness asSecond one
Present, e.g. "a@x.com"that exact valuerejected
Missing entirelynullrejected — only one document may be missing it
Explicitly nullnullrejected — same bucket as missing

Together

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

db.users.insertOne({ name: "Arya" })          // ok — email missing, counted as null
db.users.insertOne({ name: "Jon" })           // E11000 — a second null/missing value

Remember: unique: true rejects a second document with the same value — including a second missing/null value, which counts as a duplicate too. Combine with sparse for optional fields.

See also: sparse and partial indexes · upserts

Sparse and partial indexes

standardintermediate

A sparse index skips documents that are missing the indexed field entirely. A partial index skips documents based on any filter expression — sparse is really a special case of "field exists," while partial can express arbitrary conditions.

Think of it as

Both exist to shrink an index by excluding documents the app never actually queries through it. Sparse is the narrow, older tool ("only index documents that have this field"); partial is the general, newer tool ("only index documents matching this filter") — MongoDB's own docs recommend partial for anything beyond the simplest field-exists case.

text
db.<collection>.createIndex({ <field>: 1 }, { partialFilterExpression: { <condition> } })

What we're doing: Show a partial index that only covers active orders, and why an unfiltered query would not reliably use it.

partial-index-active-only.txttext
db.orders.createIndex(
  { customerId: 1 },
  { partialFilterExpression: { status: { $in: ["pending", "processing"] } } }
)

db.orders.find({ customerId: 1, status: "pending" })    // uses the partial index
db.orders.find({ customerId: 1 })                       // may not — filter doesn't match the partial condition
1
The index only contains entries for orders whose status is pending or processing — a much smaller index than one covering every order ever placed.
4
A query that does not include the same status condition cannot rely on this index to return complete results, since documents outside the filter are not in it at all.

Why this works: Most collections have a "hot," actively-queried subset (open orders, active users) and a much larger "cold" remainder — a partial index sized to the hot subset gives most of the benefit of a full index at a fraction of the memory and write cost.

Querying without the partial filter's condition and expecting complete, correct results from the partial index

Wrong

text
// Index is partial on { status: { $in: ["pending", "processing"] } }, but the query omits status entirely

Better

text
// Always include the partial filter's condition (or a query-planner-recognizable subset of it) in queries meant to use the index

What you see: A query missing documents it should have returned — because it silently fell back to a different (or no) index that does not exclude the same documents, or the results are simply incomplete for the intended use case.

Why: A partial index physically does not contain entries for excluded documents — a query that does not itself constrain to the same condition either cannot use the index at all, or would return an incomplete result set if it somehow did, so the filter has to be part of the query's own logic, not just the index definition.

Remember: Sparse excludes documents missing the field; partial excludes by any filter expression (sparse is really a special case). Both shrink the index — queries must include the partial condition to use it reliably.

See also: unique indexes

TTL indexes

coreintermediate

A TTL (time-to-live) index on a date field automatically deletes documents a set number of seconds after that date: createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 }). A background task runs every 60 seconds, so deletion is not instant.

Think of it as

A TTL index is a standing instruction to a background janitor, not a live constraint checked on every read — the janitor sweeps roughly once a minute, deletes what has expired since the last sweep, and moves on. That is why "expired 10 seconds ago" and "already deleted" are not the same claim.

text
db.<collection>.createIndex({ <dateField>: 1 }, { expireAfterSeconds: <seconds> })

What we're doing: Show a session-expiry TTL index, and the exact caveat about deletion timing.

ttl-session-expiry.txttext
db.sessions.createIndex({ lastActiveAt: 1 }, { expireAfterSeconds: 1800 })

db.sessions.insertOne({ userId: 1, lastActiveAt: new Date() })
// -> deleted sometime after 30 minutes have passed, not necessarily at exactly 30:00
1
Sessions expire 1800 seconds (30 minutes) after lastActiveAt — a common pattern for auto-expiring session/cache-like data.
4
The document becomes eligible for deletion at the 30-minute mark, but the actual delete happens on the next background sweep, which runs roughly every 60 seconds — so real deletion could be up to about a minute later.

Why this works: TTL indexes solve a genuinely common problem (auto-expiring sessions, verification codes, temporary caches) without an external cron job, but they trade exact-time deletion for that convenience — a distinction worth stating explicitly to anyone who assumes "expireAfterSeconds" means precise.

Treating TTL deletion as precise enough to enforce an exact-time business rule

Wrong

text
// Relying on a TTL index to guarantee a discount code is unusable at exactly its expiry timestamp

Better

text
// Enforce the exact-time rule in application logic (check the timestamp on read); use the TTL index only for eventual cleanup of expired documents

What you see: A document that should have been deleted at time T is still readable up to roughly a minute after T, and something that assumed exact-time deletion behaves incorrectly during that window.

Why: The TTL background task runs on its own schedule (about every 60 seconds), not the instant a document's expiry time arrives — anything that needs exact-time enforcement has to check the timestamp itself rather than relying on the document's mere existence.

From write to deletion

  1. t=0

    Document inserted

    lastActiveAt: new Date()

  2. t=1800s

    expireAfterSeconds reached

    document becomes eligible for deletion

  3. ~next sweep

    Background task runs

    roughly every 60 seconds

  4. up to ~60s later

    Document deleted

    not instant — lags expiration

  1. t=0: Document inserted — lastActiveAt: new Date()
  2. t=1800s: expireAfterSeconds reached — document becomes eligible for deletion
  3. ~next sweep: Background task runs — roughly every 60 seconds
  4. up to ~60s later: Document deleted — not instant — lags expiration

Remember: expireAfterSeconds deletes documents N seconds after a Date field — via a background task running roughly every 60 seconds, not instantly. Single-field only; never on _id.

See also: sparse and partial indexes · bson date

Text indexes and their trade-offs

standardintermediate

createIndex({ field: "text" }) enables $text search with stemming, across one or more string fields. A collection can have only one text index, it costs more RAM and write time than a scalar index, and MongoDB itself now recommends its Atlas Search product over this built-in feature for serious search.

Think of it as

A text index inverts the usual "one entry per document" shape one step further than multikey: it creates roughly one entry per unique stemmed word per document. That richness is what powers word-based search, and it is also exactly why it costs more to build, store, and update than an ordinary index.

text
db.<collection>.createIndex({ <field>: "text" })
db.<collection>.find({ $text: { $search: "<words>" } })

What we're doing: Show a basic text search, and the exact one-index-per-collection limit.

text-index-basic.txttext
db.clothing.createIndex({ description: "text" })

db.clothing.find({ $text: { $search: "silk" } })
// -> matches documents whose description contains "silk" (or a stemmed variant), regardless of position
1
This is the only text index this collection is allowed to have — a second db.clothing.createIndex({ otherField: "text" }) fails.
4
$text matches based on word presence and stemming, not substring position — it does not behave like a regex or a $regex match.

Why this works: The one-text-index-per-collection limit exists because MongoDB folds every included field into a single combined index — this is why a text index spanning several fields is written as one createIndex call, not several.

Reaching for a text index expecting relevance-ranked, phrase-aware search

Wrong

text
// Building a product-search feature on $text expecting typo-tolerance, phrase matching, and rich relevance ranking

Better

text
// Use $text for basic word-presence search; reach for MongoDB Search (Atlas) or a dedicated search engine (Elasticsearch, etc.) for anything more sophisticated

What you see: Search results feel primitive compared to user expectations — no typo tolerance, no phrase weighting, no fuzzy matching — despite the index existing and working "correctly" by its own definition.

Why: A text index is a basic word-match tool with stemming, not a search engine — MongoDB's own documentation now recommends its separate Search/Vector Search products for anything beyond that, which is exactly the roadmap's "know when database text search is insufficient" framing.

Remember: createIndex({ field: "text" }) + $text: { $search }. One text index per collection (can span fields). Costs more RAM/write time than a scalar index — reach for MongoDB Search or a dedicated engine for real relevance ranking.

See also: multikey indexes

Advertisement

Ordering for real queries

Why a compound index's field order — not just which fields it contains — decides whether it serves a query's filter, sort, and range together.

Key ordering: equality, sort, and range

coreintermediate

Within a compound index, equality fields should come first, sort fields next, and range fields last — the same principle section 11 names ESR (equality, sort, range). Getting the order right lets a single index serve filtering, sorting, and ranging without an extra in-memory sort step.

Think of it as

Picture the index as nested sorted groups: equality fields partition the index into small, exact buckets; sort fields determine the order within each bucket; range fields determine how far each bucket needs scanning. Putting a range field before a sort field breaks this — once the index starts ranging, the remaining fields are no longer in a single predictable order, so MongoDB cannot use the index for the sort and needs a separate, more expensive in-memory sort.

text
db.<collection>.createIndex({ <equalityField>: 1, <sortField>: 1, <rangeField>: 1 })

What we're doing: Show the same query served two ways — an index in equality-sort-range order avoiding an in-memory sort, versus one that forces it.

esr-order-vs-wrong-order.txttext
// Correct order — equality, sort, range:
db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

// Wrong order — range before sort:
db.orders.createIndex({ status: 1, total: 1, createdAt: -1 })
// -> the same query now needs an in-memory sort, because ranging on total breaks the createdAt ordering
2
With range last, the index still returns documents pre-sorted by createdAt within each status bucket — the range on total is applied within that order without breaking it.
6
With range (total) before sort (createdAt), the index no longer guarantees createdAt order across the range of matching totals — MongoDB has to collect the matches and sort them separately.

Why this works: The index only guarantees a single, predictable order up to the point a range condition starts including multiple values per equality bucket — placing sort before range keeps that guarantee intact for exactly as long as the query needs it.

Ordering a compound index by "the fields I query on" without distinguishing their roles

Wrong

text
// Fields added to the index in whatever order they were written in the query, without asking which is equality/sort/range

Better

text
// Classify each field's role first (equality/sort/range), then order the index equality → sort → range

What you see: explain() shows a SORT stage after the index scan, meaning MongoDB is sorting results in memory even though a "relevant" index exists.

Why: A compound index's usefulness depends on field order matching each field's role in the query, not just which fields happen to appear in it — the SORT stage in explain() is the concrete, checkable sign that the current order is not letting the index do that work.

Equality, then sort, then range

Equality: status

partitions into exact buckets

Sort: createdAt

ordered within each bucket

Range: total

scanned within the ordered result

  1. Equality: status — partitions into exact buckets
  2. Sort: createdAt — ordered within each bucket
  3. Range: total — scanned within the ordered result

Field role → position in the compound index

Field role → position in the compound index
RoleExamplePosition
Equality{ status: "shipped" }first
Sort.sort({ createdAt: -1 })second
Range{ total: { $gte: 100 } }last

Together

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

db.orders.find({ status: "shipped", total: { $gte: 100 } }).sort({ createdAt: -1 })
// -> equality (status), then sort (createdAt), then range (total) — matches the index order, no extra sort step

Remember: Order a compound index equality, then sort, then range (ESR). A range field before a sort field forces an in-memory SORT stage even if the sort field is technically indexed.

See also: compound indexes · esr index reasoning

Advertisement