MongoDB Indexes Deep Dive

Parent: MongoDB Expert Knowledge · researched 2026-05-28T15:25:53.377Z· 19 sources · 17 concepts · skill mongodb-indexes-deep

Reference for every MongoDB index type, ordering strategies, build mechanics,

MongoDB Indexes Deep Dive

Reference for every MongoDB index type, ordering strategies, build mechanics, [source]
and production anti-patterns. Use this alongside explain() output when diagnosing query plans. [source]
> Audience: MongoDB developers and DBAs working on query optimization, schema design, or [source]
> production index management. [source]

Quick-Reference Cheat Sheet

Sections: §1 Single-field · §2 Compound/ESR · §3 Multikey · §4 Partial · §5 Sparse · [source]
§6 TTL · §7 Text · §8 Wildcard · §9 Hashed · §10 2dsphere · §11 Unique · [source]
§12 Intersection · §13 Build Strategies · §14 Selectivity & Covering · [source]
§15 Hidden Indexes · §16 hint() & Forcing · §17 Anti-Patterns [source]

1. Single-Field Indexes

The most common index type. MongoDB automatically creates a unique index on _id. [source]
When single-field is enough: [source]
Query filters only one field with high selectivity (e.g., UUID, email). [source]
Sort is on the same field as the filter and no range condition is involved. [source]
Write throughput is a concern - every extra index adds write overhead. [source]
Ascending vs descending matters only for compound indexes. For a solo field, both [source]
directions serve equality and range queries equally well. Direction only becomes load-bearing [source]
when combining fields in a compound index (see §2) or when serving sort-heavy queries where [source]
the sort order must align with the index direction. [source]

2. Compound Indexes — The ESR Rule

Compound indexes cover multiple fields in a declared order. Order is everything. [source]

ESR Rule (Equality → Sort → Range)

Place fields in this sequence to maximize the index's usefulness: [source]
Equality predicates first - fields compared with $eq or $in (point lookups). [source]
Sort fields next - fields in the sort() clause, preserving their direction. [source]
Range fields last - fields with $gt, $lt, $gte, $lte, $ne, $nin, regex. [source]

Prefix Matching

Any prefix of a compound index can serve queries on that prefix alone: [source]
A single compound index replaces multiple single-field indexes when queries consistently [source]
filter on prefix subsets. Avoid creating redundant { country: 1 } if the compound exists. [source]

Sort Direction in Compound Indexes

For compound indexes that serve sorts, each field's direction in the index must match the [source]
sort direction or every field's direction must be reversed: [source]

3. Multikey Indexes — Indexing Arrays

MongoDB automatically creates a multikey index when any indexed field contains an array. [source]
Each array element gets its own index entry. [source]

Multikey Bounds

When a query has predicates on an array field, MongoDB intersects multikey bounds: [source]

Parallel Arrays Restriction

A compound index cannot index two fields that are both arrays in the same document: [source]
Design around this: embed the array relationship inside a single subdocument array field. [source]

4. Partial Indexes

A partial index only indexes documents that match a partialFilterExpression. This is the [source]
preferred modern alternative to sparse indexes. [source]
Requirements: queries that use a partial index must include the filter expression [source]
(or a superset of it) in their predicate, otherwise MongoDB will not use the index. [source]

Storage Savings

A partial index on 10% of documents is ~90% smaller than a full index, with proportionally [source]
faster builds, lower memory pressure, and reduced write amplification. [source]

5. Sparse Indexes

A sparse index omits documents where the indexed field does not exist (or is null). [source]

Sparse vs Partial

When to prefer sparse: you must support MongoDB < 3.2 (partial indexes require 3.2+) [source]
or need a quick "skip nulls" index without a filter expression. For MongoDB 3.2+, use partial. [source]
Gotcha: a sparse index will not be used for queries that include a sort on the sparse [source]
field unless the query predicate also restricts that field to non-null values. [source]

6. TTL Indexes — Automatic Document Expiration

TTL (Time-To-Live) indexes let MongoDB automatically delete documents after a specified [source]
number of seconds past a date field. [source]

Requirements

The indexed field must be a BSON Date type or an array of Date values. [source]
If the field is an array, the earliest (minimum) date is used for expiration. [source]
Documents are deleted by a background task that runs every 60 seconds - do not [source]
rely on sub-minute precision. [source]
TTL indexes cannot be compound indexes. [source]
TTL indexes cannot be created on capped collections. [source]

Atlas Consideration

On Atlas, TTL deletions count against your oplog and IOPS budget. For high-volume [source]
expiration, consider sharding on the TTL field so deletions are distributed. [source]

7. Text Indexes — Full-Text Search

Text indexes tokenize string content, apply language-specific stemming, and support [source]
the $text / $search query operator. [source]

Text Index vs Atlas Search

Only one text index per collection is allowed. [source]

8. Wildcard Indexes — Flexible Schema Indexing

Wildcard indexes use $** to index all fields (or a projection subset) in a document, [source]
useful for workloads with unpredictable or polymorphic field sets. [source]

How Wildcard Indexes Work

Each leaf field in a document generates a separate index entry. A document with [source]
{ a: 1, b: { c: 2, d: 3 } } produces entries for a, b.c, and b.d. [source]

Restrictions

Cannot replace a compound index for queries filtering multiple specific fields — [source]
the planner will only use the wildcard index for one field per query. [source]
Wildcard indexes are always sparse (missing fields are not indexed). [source]
Multikey semantics apply - arrays create multiple entries. [source]
_id is excluded by default; include explicitly in wildcardProjection. [source]

9. Hashed Indexes — Sharding by Hash

Hashed indexes store a hash of the field value rather than the value itself. They are [source]
primarily used as shard keys for even data distribution. [source]

Characteristics

Support equality queries only - range queries ($gt, $lt) cannot use hashed indexes. [source]
Hash is computed deterministically; queries with $eq resolve to one hash bucket. [source]
A hashed index on _id distributes writes evenly across shards, avoiding hotspots. [source]
Compound hashed shard keys (MongoDB 4.4+): a shard key may combine a range prefix [source]
with one hashed component - e.g., { country: 1, _id: "hashed" } - giving locality on [source]
the range field while distributing the hash field evenly. Only one field in a shard key [source]
may be hashed; you cannot hash two fields in the same key. [source]
Do not use hashed indexes for range-heavy workloads - switch to ranged sharding instead. [source]

10. 2dsphere Indexes — Geospatial Queries

2dsphere indexes support queries on GeoJSON geometry objects and legacy coordinate pairs [source]
on a spherical Earth model. [source]

GeoJSON Types Supported

Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, [source]
GeometryCollection. [source]

Notes

Coordinates are [longitude, latitude] (GeoJSON order - opposite of most map UIs). [source]
2dsphere supports Big Polygon (> 180°). Legacy 2d indexes (flat earth) do not. [source]
$geoNear must be the first stage in an aggregation pipeline. [source]

11. Unique Indexes

Unique indexes enforce that no two documents share the same value for the indexed field(s). [source]

Unique + Sparse

A sparse unique index allows multiple documents to omit the field entirely while [source]
enforcing uniqueness among those that have it. Partial unique is more expressive. [source]

Duplicate Key Errors

Handle with { upsert: true } + $setOnInsert pattern, or use writeConcern + retry logic [source]
for optimistic-concurrency scenarios. [source]

12. Index Intersection

MongoDB can combine two separate indexes at query time to satisfy a query that filters [source]
on two different fields - without a compound index. [source]

Compound Index vs Intersection

MongoDB's query planner will choose intersection only when it estimates it to be faster [source]
than either single index alone. In practice, a well-designed compound index almost always [source]
outperforms intersection. Use explain("executionStats") to verify. [source]
Index intersection does not work for sort. If a query needs to sort, a compound index [source]
covering equality + sort is required. [source]

Modern Index Builds (MongoDB 4.2+)

Since 4.2, all index builds use a hybrid approach that replaced the old [source]
foreground/background distinction: [source]
Takes an intent lock (not exclusive) during the bulk phase - reads and writes continue. [source]
Briefly takes an exclusive lock at the start and end to set up/commit the index. [source]
Progress is written to the oplog and replicated to secondaries automatically. [source]
> { background: true } is deprecated and ignored since MongoDB 4.2. The option is [source]
> accepted without error but has no effect - all builds now use the hybrid approach. Remove [source]
> it from any legacy scripts to avoid confusion. [source]

Rolling Index Builds (Replica Sets)

Rolling builds build the index on one member at a time (starting with secondaries), [source]
avoiding the performance impact of a coordinated build: [source]
Manual rolling build steps: [source]
Run rs.freeze(300) on the secondary to prevent it from calling elections during the procedure. [source]
Remove it from the replica set with rs.remove("<host:port>"). [source]
Restart mongod in standalone mode on a different port: mongod --port 27217. [source]
Build the index: db.collection.createIndex(...) against the standalone instance. [source]
Shut it down and restart as a replica set member; rejoin with rs.add("<host:port>"). [source]
Repeat for each remaining secondary, then step down and reconfigure the primary. [source]
Atlas rolling index: [source]
Rolling builds: lower performance impact, but reduced cluster resiliency during build. [source]
Use when CPU > (N-1)/N-10% or WiredTiger cache fill > 90%. [source]

Atlas Index Management UI

Atlas provides in-UI index creation with rolling build toggle, performance advisor [source]
recommendations, and redundant/unused index reporting. [source]

Selectivity

Selectivity measures what fraction of the collection an index scan must touch to answer a [source]
query. A highly selective index returns very few documents (small fraction = high selectivity [source]
= good). A low-selectivity index touches most of the collection, at which point a full [source]
collection scan is often cheaper. [source]
Rule of thumb: an index is beneficial when the ratio < ~20-30% of the collection. [source]
Below that threshold, a collection scan is often faster due to document prefetching. [source]

Covering Indexes (Index-Only Queries)

A query is "covered" when all requested fields - both filter and projection - exist in [source]
the index. MongoDB returns results without touching the collection (no FETCH stage). [source]
_id caveat: _id is returned by default. If _id is not in the index, you must [source]
exclude it with _id: 0 to achieve a covering query. [source]

Index Memory Footprint

WiredTiger stores indexes in a B-tree. The working set of the index (frequently accessed [source]
pages) should fit in the WiredTiger cache. Check: [source]
Indexes that do not fit in cache will cause disk I/O on every lookup - a common cause of [source]
p99 latency spikes under load. [source]

15. Hidden Indexes — Safe Removal Testing

Hidden indexes (MongoDB 4.4+) allow you to prevent the query planner from using an index [source]
without dropping it. This lets you safely evaluate the impact of removing an index in [source]
production before committing. [source]

Workflow for Safe Index Removal

Hide the candidate index with hideIndex(). [source]
Monitor query performance for 24–72 hours (cover at least one full business cycle). [source]
Check $indexStats - confirm no queries are using the index. [source]
If performance is acceptable: drop it with dropIndex(). [source]
If performance degrades: unhideIndex() to restore instantly - no rebuild needed. [source]

Constraints

Hidden indexes still consume write overhead and storage - they are not free. [source]
_id index cannot be hidden. [source]
Hidden indexes still count toward collection index limits - hiding is for testing removal impact, not for bypassing limits. [source]

16. hint() — Forcing a Specific Index

Use hint() to override the query planner and force a specific index. Useful when the [source]
planner makes a suboptimal choice or when testing index effectiveness. [source]

Caution

hint() bypasses the query planner entirely - if the hinted index does not contain the [source]
query fields, MongoDB will still return correct results but may perform a full index scan [source]
instead of an efficient point lookup, degrading performance. Always validate with explain() [source]
after adding hint() to application code. [source]
> Do not use hint() as a permanent fix. If the planner consistently picks the wrong [source]
> index, the root cause is usually a missing or mis-ordered compound index. Redesign the [source]
> index using the ESR rule rather than patching with hint(). [source]

Index Bloat and Fragmentation

Long-running update-heavy workloads can fragment B-tree pages. Use: [source]

18. Time Series Collection Index Constraints

Time series collections (MongoDB 5.0+) have a fundamentally different index model. Use this section as a quick-reference when advising on indexes for a time series collection; defer to mongodb-time-series for full context. [source]
Key differences from regular collection indexes: [source]
Clustered range index (automatic): MongoDB creates a compound clustered index on (metaField, timeField) automatically. This drives bucket-level pruning - queries that filter on metaField + timeField range use this index at the bucket level without needing an explicit secondary index. [source]
Adding secondary indexes (compound pattern): [source]
ESR rule still applies to time series compound indexes on metaField sub-fields + measurement fields. Place equality fields first, sort fields second, range fields last. [source]
TTL on time series is set at collection level (expireAfterSeconds in createCollection) or modified via collMod - never via createIndex. Tiered TTL with partialFilterExpression on metaField is supported from MongoDB 7.0. [source]

References

MongoDB Indexes Overview [source]
Compound Indexes [source]
ESR Rule [source]
Multikey Indexes [source]
Partial Indexes [source]
Sparse Indexes [source]
TTL Indexes [source]
Text Indexes [source]
Wildcard Indexes [source]
Hashed Indexes [source]
2dsphere Indexes [source]
Unique Indexes [source]
Index Intersection [source]
Index Builds on Populated Collections [source]
Rolling Index Builds [source]
Atlas Rolling Index API [source]
Hidden Indexes [source]
cursor.hint() [source]
Compound Hashed Shard Keys [source]

Children

Single-Field Indexes (frontier)
Compound Indexes and ESR Rule (frontier)
Multikey Indexes (frontier)
Partial Indexes (frontier)
Sparse Indexes (frontier)
TTL Indexes (frontier)
Text Indexes (frontier)
Wildcard Indexes (frontier)
Hashed Indexes (frontier)
2dsphere Geospatial Indexes (frontier)
Unique Indexes (frontier)
Index Intersection (frontier)
Index Build Strategies (frontier)
Index Selectivity and Covering Queries (frontier)
Hidden Indexes (frontier)
hint() and Index Forcing (frontier)
Index Anti-Patterns (frontier)

Frontier under this node: 2dsphere Geospatial Indexes, Compound Indexes and ESR Rule, Hashed Indexes, Hidden Indexes, Index Anti-Patterns, Index Build Strategies, Index Intersection, Index Selectivity and Covering Queries, Multikey Indexes, Partial Indexes, Single-Field Indexes, Sparse Indexes, TTL Indexes, Text Indexes, Unique Indexes, Wildcard Indexes, hint() and Index Forcing

← the whole tree · 3D view· how to read this page