beginnerintermediateadvanced

MongoDB · Indexing Fundamentals

Indexing Fundamentals

Why indexes exist, the index types, and the equality-sort-range ordering rule

No index → COLLSCANevery document checked, not just matches
Compound: order mattersserves a prefix, not any field alone
unique + missing = nullonly one document may omit the field
TTL is not instanta background sweep runs roughly every 60s

1.Why indexes exist

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.

Key features

No index → COLLSCAN: every document in the collection is checked against the query
An index → IXSCAN: MongoDB walks a sorted structure and jumps to matching values
Indexes are B-trees, so they support equality, range, and sorted-order queries efficiently
Every collection has one index by default: a unique index on _id
An index costs write performance — every insert/update to an indexed field also updates the index

Scan vs. index, at a glance

Scan vs. index, at a glance
Property	Without an index (COLLSCAN)	With an index (IXSCAN)
Read cost	grows with collection size	grows with matching documents, not collection size
Write cost	no index maintenance	each write also updates the index
Sorted output	requires an in-memory sort	can return already sorted, if the index matches

What an index changes about a query

no index

COLLSCAN — check every document

createIndex

a sorted structure

IXSCAN

jump to matches

Command

text

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

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.

2.Single-field indexes

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.

Key features

db.collection.createIndex({ field: 1 }) — 1 for ascending, -1 for descending
A single-field index supports equality, range, and sort queries on that one field
Direction (1 vs. -1) does not matter on its own for a single-field index — MongoDB can scan either way
_id's default index is a single-field, unique index, automatically created and undroppable
A single-field index on an array field automatically becomes multikey — see the array-indexing concept

Command

text

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

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.

3.Compound indexes

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.

Key features

createIndex({ a: 1, b: 1 }) sorts by a first, then by b within each a value
Field order in the index definition is a deliberate design choice, not incidental
Supports queries on the leading field(s) in that order — the prefix rule, detailed in section 11
One well-designed compound index often replaces several single-field indexes for the same query set
Up to 32 fields per compound index (a hard MongoDB limit, rarely approached in practice)

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

What a compound index on { a: 1, b: 1 } actually serves
Query filters on	Uses this index?
a only	yes — a is the leading field
a and b	yes — both fields, in index order
b only	no — b alone is not a usable prefix

Sorted by customerId first, then createdAt within each customer

customerId: 1

primary sort key

customerId: 1, createdAt: 1

sub-sorted within each customer

Command

text

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

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.

4.Multikey indexes

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.

Key features

Indexing an array field automatically makes the index multikey — no special option needed
One index entry is created per array element, all pointing back to the same document
A query on the field matches if any element in the array matches
Section 12 (Multikey and Array Indexes) covers the deeper design implications and restrictions
A multikey index generally costs more per write than a scalar index, proportional to array length

Command

text

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

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.

5.Unique indexes

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.

Key features

db.collection.createIndex({ field: 1 }, { unique: true })
A duplicate value on insert or update fails with an E11000 duplicate key error
A missing field is treated as null for uniqueness — only one document may have a missing/null value
A compound unique index enforces uniqueness on the combination of fields, not each field separately
Applying unique: true to an existing collection with real duplicates fails until they are resolved

Unique index behavior by field state

Unique index behavior by field state
Field state	Counts toward uniqueness as	Second one
Present, e.g. "a@x.com"	that exact value	rejected
Missing entirely	null	rejected — only one document may be missing it
Explicitly null	null	rejected — same bucket as missing

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

Command

text

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

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.

6.Sparse and partial indexes

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.

Key features

Sparse: { sparse: true } — excludes documents where the indexed field is missing
Partial: { partialFilterExpression: {...} } — excludes documents not matching an arbitrary filter
A partial index with { field: { $exists: true } } reproduces sparse behavior, plus more expressive filters
Smaller index = less memory, less write overhead — the main reason to reach for either
A query must include the partial filter's condition (or a subset) to reliably use a partial index

Command

text

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

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.

7.TTL indexes

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.

Key features

createIndex({ dateField: 1 }, { expireAfterSeconds: N }) — deletes N seconds after the field's date value
The indexed field must hold a Date (or an array of Dates) — other types are never expired
A background task runs roughly every 60 seconds, so deletion lags expiration by up to about a minute
A document missing the indexed field, or where it is not a Date, is never expired by this index
TTL indexes must be single-field — no compound TTL index, and _id cannot be a TTL index

From write to deletion

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

Command

text

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

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.

8.Text indexes and their trade-offs

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.

Key features

createIndex({ field: "text" }) — or multiple fields in one text index
Only one text index is allowed per collection, though it can cover several fields
Query with $text: { $search: "..." } after the index exists
Stemming and language support reduce "running"/"ran"/"run" to the same match
Cannot serve a covered query, and stores no phrase/proximity information — a single-word match tool, not a ranked search engine

Command

text

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

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.

9.Key ordering: equality, sort, and range

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.

Key features

Equality fields (exact match, e.g. { status: "shipped" }) should lead a compound index
Sort fields should come next, in the same field order and direction the query sorts by
Range fields ($gt, $lt, $in with multiple values) should come last
A range field placed before a sort field prevents the index from also serving that sort
This ordering (equality, sort, range) is formalized as the ESR rule in section 11

Field role → position in the compound index

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

Equality, then sort, then range

Equality: status

partitions into exact buckets

Sort: createdAt

ordered within each bucket

Range: total

scanned within the ordered result

Command

text

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

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.

Spotted a mistake on this page? Tell us.

Read Sheet