MongoDB Indexes Deep Dive

Parent: MongoDB Expert Knowledge · Published reference · snapshot 2026-09-08 · skill mongodb-expert/references/mongodb-indexes-deep.md

↓ Facts as markdown all context files

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

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

MongoDB Indexes Deep Dive

Reference for every MongoDB index type, ordering strategies, build mechanics, and production anti-patterns. Use this alongside explain() output when diagnosing query plans. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#mongodb-indexes-deep-dive]
> Audience: MongoDB developers and DBAs working on query optimization, schema design, or > production index management. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#mongodb-indexes-deep-dive]

Quick-Reference Cheat Sheet

Sections: §1 Single-field · §2 Compound/ESR · §3 Multikey · §4 Partial · §5 Sparse · §6 TTL · §7 Text · §8 Wildcard · §9 Hashed · §10 2dsphere · §11 Unique · §12 Intersection · §13 Build Strategies · §14 Selectivity & Covering · §15 Hidden Indexes · §16 hint() & Forcing · §17 Anti-Patterns [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#quick-reference-cheat-sheet]

1. Single-Field Indexes

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

2. Compound Indexes — The ESR Rule

Compound indexes cover multiple fields in a declared order. Order is everything. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#2-compound-indexes-the-esr-rule]

ESR Rule (Equality → Sort → Range)

Place fields in this sequence to maximize the index's usefulness: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#esr-rule-equality-sort-range]
- Equality predicates first - fields compared with $eq or $in (point lookups). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#esr-rule-equality-sort-range]
- Sort fields next - fields in the sort() clause, preserving their direction. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#esr-rule-equality-sort-range]
- Range fields last - fields with $gt, $lt, $gte, $lte, $ne, $nin, regex. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#esr-rule-equality-sort-range]

Prefix Matching

Any prefix of a compound index can serve queries on that prefix alone: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#prefix-matching]
A single compound index replaces multiple single-field indexes when queries consistently filter on prefix subsets. Avoid creating redundant { country: 1 } if the compound exists. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#prefix-matching]

Sort Direction in Compound Indexes

For compound indexes that serve sorts, each field's direction in the index must match the sort direction or every field's direction must be reversed: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#sort-direction-in-compound-indexes]

3. Multikey Indexes — Indexing Arrays

MongoDB automatically creates a multikey index when any indexed field contains an array. Each array element gets its own index entry. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#3-multikey-indexes-indexing-arrays]

Multikey Bounds

When a query has predicates on an array field, MongoDB intersects multikey bounds: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#multikey-bounds]

Parallel Arrays Restriction

A compound index cannot index two fields that are both arrays in the same document: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#parallel-arrays-restriction]
Design around this: embed the array relationship inside a single subdocument array field. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#parallel-arrays-restriction]

4. Partial Indexes

A partial index only indexes documents that match a partialFilterExpression. This is the preferred modern alternative to sparse indexes. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#4-partial-indexes]
Requirements: queries that use a partial index must include the filter expression (or a superset of it) in their predicate, otherwise MongoDB will not use the index. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#4-partial-indexes]

Storage Savings

A partial index on 10% of documents is ~90% smaller than a full index, with proportionally faster builds, lower memory pressure, and reduced write amplification. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#storage-savings]

5. Sparse Indexes

A sparse index omits documents where the indexed field does not exist (or is null). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#5-sparse-indexes]

Sparse vs Partial

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

6. TTL Indexes — Automatic Document Expiration

TTL (Time-To-Live) indexes let MongoDB automatically delete documents after a specified number of seconds past a date field. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#6-ttl-indexes-automatic-document-expiration]

Requirements

The indexed field must be a BSON Date type or an array of Date values. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#requirements]
If the field is an array, the earliest (minimum) date is used for expiration. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#requirements]
Documents are deleted by a background task that runs every 60 seconds - do not rely on sub-minute precision. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#requirements]
TTL indexes cannot be compound indexes. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#requirements]
TTL indexes cannot be created on capped collections. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#requirements]

Atlas Consideration

On Atlas, TTL deletions count against your oplog and IOPS budget. For high-volume expiration, consider sharding on the TTL field so deletions are distributed. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#atlas-consideration]

7. Text Indexes — Full-Text Search

Text indexes tokenize string content, apply language-specific stemming, and support the $text / $search query operator. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#7-text-indexes-full-text-search]

Text Index vs Atlas Search

Only one text index per collection is allowed. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#text-index-vs-atlas-search]

8. Wildcard Indexes — Flexible Schema Indexing

Wildcard indexes use $** to index all fields (or a projection subset) in a document, useful for workloads with unpredictable or polymorphic field sets. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#8-wildcard-indexes-flexible-schema-indexing]

How Wildcard Indexes Work

Each leaf field in a document generates a separate index entry. A document with { a: 1, b: { c: 2, d: 3 } } produces entries for a, b.c, and b.d. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#how-wildcard-indexes-work]

Restrictions

Cannot replace a compound index for queries filtering multiple specific fields - the planner will only use the wildcard index for one field per query. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#restrictions]
Wildcard indexes are always sparse (missing fields are not indexed). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#restrictions]
Multikey semantics apply - arrays create multiple entries. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#restrictions]
_id is excluded by default; include explicitly in wildcardProjection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#restrictions]

9. Hashed Indexes — Sharding by Hash

Hashed indexes store a hash of the field value rather than the value itself. They are primarily used as shard keys for even data distribution. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#9-hashed-indexes-sharding-by-hash]

Characteristics

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

10. 2dsphere Indexes — Geospatial Queries

2dsphere indexes support queries on GeoJSON geometry objects and legacy coordinate pairs on a spherical Earth model. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#10-2dsphere-indexes-geospatial-queries]

GeoJSON Types Supported

Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#geojson-types-supported]

Notes

Coordinates are [longitude, latitude] (GeoJSON order - opposite of most map UIs). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#notes]
2dsphere supports Big Polygon (> 180°). Legacy 2d indexes (flat earth) do not. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#notes]
$geoNear must be the first stage in an aggregation pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#notes]

11. Unique Indexes

Unique indexes enforce that no two documents share the same value for the indexed field(s). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#11-unique-indexes]

Unique + Sparse

A sparse unique index allows multiple documents to omit the field entirely while enforcing uniqueness among those that have it. Partial unique is more expressive. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#unique-sparse]

Duplicate Key Errors

Handle with { upsert: true } + $setOnInsert pattern, or use writeConcern + retry logic for optimistic-concurrency scenarios. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#duplicate-key-errors]

12. Index Intersection

MongoDB can combine two separate indexes at query time to satisfy a query that filters on two different fields - without a compound index. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#12-index-intersection]

Compound Index vs Intersection

MongoDB's query planner will choose intersection only when it estimates it to be faster than either single index alone. In practice, a well-designed compound index almost always outperforms intersection. Use explain("executionStats") to verify. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#compound-index-vs-intersection]
Index intersection does not work for sort. If a query needs to sort, a compound index covering equality + sort is required. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#compound-index-vs-intersection]

Modern Index Builds (MongoDB 4.2+)

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

Rolling Index Builds (Replica Sets)

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

Atlas Index Management UI

Atlas provides in-UI index creation with rolling build toggle, performance advisor recommendations, and redundant/unused index reporting. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#atlas-index-management-ui]

Selectivity

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

Covering Indexes (Index-Only Queries)

A query is "covered" when all requested fields - both filter and projection - exist in the index. MongoDB returns results without touching the collection (no FETCH stage). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#covering-indexes-index-only-queries]
_id caveat: _id is returned by default. If _id is not in the index, you must exclude it with _id: 0 to achieve a covering query. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#covering-indexes-index-only-queries]

Index Memory Footprint

WiredTiger stores indexes in a B-tree. The working set of the index (frequently accessed pages) should fit in the WiredTiger cache. Check: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#index-memory-footprint]
Indexes that do not fit in cache will cause disk I/O on every lookup - a common cause of p99 latency spikes under load. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#index-memory-footprint]

15. Hidden Indexes — Safe Removal Testing

Hidden indexes (MongoDB 4.4+) allow you to prevent the query planner from using an index without dropping it. This lets you safely evaluate the impact of removing an index in production before committing. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#15-hidden-indexes-safe-removal-testing]

Workflow for Safe Index Removal

Hide the candidate index with hideIndex(). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#workflow-for-safe-index-removal]
Monitor query performance for 24–72 hours (cover at least one full business cycle). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#workflow-for-safe-index-removal]
Check $indexStats - confirm no queries are using the index. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#workflow-for-safe-index-removal]
If performance is acceptable: drop it with dropIndex(). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#workflow-for-safe-index-removal]
If performance degrades: unhideIndex() to restore instantly - no rebuild needed. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#workflow-for-safe-index-removal]

Constraints

Hidden indexes still consume write overhead and storage - they are not free. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#constraints]
_id index cannot be hidden. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#constraints]
Hidden indexes still count toward collection index limits - hiding is for testing removal impact, not for bypassing limits. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#constraints]

16. hint() — Forcing a Specific Index

Use hint() to override the query planner and force a specific index. Useful when the planner makes a suboptimal choice or when testing index effectiveness. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#16-hint-forcing-a-specific-index]

Caution

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

Index Bloat and Fragmentation

Long-running update-heavy workloads can fragment B-tree pages. Use: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#index-bloat-and-fragmentation]

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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#18-time-series-collection-index-constraints]
Key differences from regular collection indexes: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#18-time-series-collection-index-constraints]
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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#18-time-series-collection-index-constraints]
Adding secondary indexes (compound pattern): [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#18-time-series-collection-index-constraints]
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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#18-time-series-collection-index-constraints]
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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#18-time-series-collection-index-constraints]

References

MongoDB Indexes Overview [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Compound Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
ESR Rule [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Multikey Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Partial Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Sparse Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
TTL Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Text Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Wildcard Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Hashed Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
2dsphere Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Unique Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Index Intersection [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Index Builds on Populated Collections [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Rolling Index Builds [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Atlas Rolling Index API [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Hidden Indexes [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
cursor.hint() [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]
Compound Hashed Shard Keys [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-indexes-deep/#references]

Where this helps

Diagnosing a slow query in production by pairing explain() output with the right index type — deciding between a compound index, a partial index, or index intersection based on the actual filter/sort shape. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Designing indexes for a new collection from its actual query patterns, using the ESR rule (Equality -> Sort -> Range) instead of guessing at field order. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Choosing between sparse, partial, TTL, or wildcard indexes when a collection has an unusual or evolving schema shape. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Safely testing whether an index can be dropped in production, using hidden indexes to measure real impact before committing to the drop. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Project ideas

Build an index-audit script that runs $indexStats across a collection, flags zero-usage indexes, and stages them as hidden (not dropped) for a 24-72 hour production observation window. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Implement a covering-query checker that compares a query's filter and projection fields against an existing index definition and reports whether the query would trigger a FETCH stage. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Build a shard-key advisor that recommends a compound hashed shard key (range prefix + hashed component) for collections with a natural range field but risk of write hotspots. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Prototype a rolling-index-build orchestrator for a self-managed replica set that automates the freeze, remove, standalone build, and rejoin sequence for large collections. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Antipatterns

Reaching for hint() as a permanent fix when the query planner picks a suboptimal index, instead of redesigning the compound index using the ESR rule. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Creating a compound index with fields in the wrong order (e.g., range before equality), which produces an index that technically exists but doesn't serve the query efficiently. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Relying on index intersection instead of a well-designed compound index — the planner only chooses intersection when it estimates it faster, and it never serves a sort. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Leaving legacy { background: true } options in index-creation scripts, which are silently ignored since MongoDB 4.2 and create false confidence about build behavior. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Known issues

The background/foreground index build distinction was replaced by a hybrid approach in MongoDB 4.2+, so { background: true } is accepted but has zero effect — a common source of confusion when reading older scripts. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
A compound index cannot include two array fields from the same document (the parallel arrays restriction), which forces a schema workaround rather than a simple index change. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
TTL deletions run on a background task every 60 seconds, so TTL indexes cannot guarantee sub-minute expiration precision, and at high volume they consume real oplog and IOPS budget on Atlas. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
A sparse index silently fails to serve a query that sorts on the sparse field unless the query predicate also restricts that field to non-null values — a gotcha that can make a seemingly-indexed sort fall back to an in-memory sort. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

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)
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: 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