137: MongoDB Indexes and Query Plans — Compound, Multikey, Unique, TTL, Text, Geospatial, Partial, and `explain()`

Learning objectives

You will learn to:

explain how indexes trade storage/write cost for query performance;
create single-field and compound indexes;
understand index prefix/order;
apply Equality-Sort-Range thinking;
understand multikey indexes;
enforce uniqueness;
use partial/sparse/TTL indexes appropriately;
understand text and geospatial indexes;
understand wildcard and Atlas Search distinctions;
read explain() at a practical level;
recognize COLLSCAN versus IXSCAN;
reason about covered queries;
avoid over-indexing.

Why indexes matter

Without useful index:

text
find matching documents
→ inspect many/all documents

With useful index:

text
navigate index
→ locate matching records

Index is an additional data structure maintained on writes.

Costs:

disk;
memory/cache;
insert/update/delete overhead;
operational complexity.

Do not add indexes to every field.

Default `_id`

MongoDB creates unique _id index automatically.

Query by _id is efficient.

Again: efficient ID query is not authorization.

Single-field index

javascript
db.tasks.createIndex({
  tenantId: 1
})

Direction matters less for a lone field equality, but matters in compound sort interactions.

Compound index

Common query:

javascript
db.tasks.find({
  tenantId,
  status: "open"
}).sort({
  createdAt: -1
}).limit(25)

Candidate:

javascript
db.tasks.createIndex({
  tenantId: 1,
  status: 1,
  createdAt: -1,
  _id: -1
})

This supports:

tenant equality;
status equality;
sort;
pagination tiebreaker.

Index prefixes

Index:

javascript
{
  tenantId: 1,
  status: 1,
  createdAt: -1
}

can support leading-prefix queries such as:

text
tenantId
tenantId + status
tenantId + status + createdAt

A query only on:

text
status

cannot generally use the compound index as effectively as if status were leading.

Design indexes from actual query patterns.

Equality, Sort, Range (ESR)

Useful guideline:

text
equality fields
then sort fields
then range fields

Example:

text
tenantId = ?
status = ?
sort createdAt desc
createdAt < cursor

Index:

javascript
{
  tenantId: 1,
  status: 1,
  createdAt: -1,
  _id: -1
}

Real query planner behavior can differ. Verify with explain.

Do not treat ESR as a law that replaces measurement.

Sort direction

Compound index can support certain forward/reverse order combinations.

If query sorts:

javascript
{
  createdAt: -1,
  _id: -1
}

match index direction accordingly.

Mixed directions matter.

Test explain.

Range

javascript
{
  createdAt: {
    $gte: start,
    $lt: end
  }
}

After range field, later index fields may have reduced ability for filtering/sort depending query.

Think order.

Unique index

javascript
db.users.createIndex(
  {
    tenantId: 1,
    emailNormalized: 1
  },
  {
    unique: true
  }
)

This enforces tenant-scoped uniqueness under concurrency.

Application pre-check:

text
SELECT/find existing first
then insert

is not enough; concurrent requests race.

Database unique constraint/index is authority.

Catch duplicate key error and map to 409/422.

Case-insensitive uniqueness

Do not rely on naive lowercase if Unicode/email/domain requirements are complex.

Options:

normalized stored field;
collation-aware index.

Collation affects comparison/sort/index matching.

Design normalization explicitly.

Partial index

Index only documents matching expression.

Example active users:

javascript
db.users.createIndex(
  {
    tenantId: 1,
    emailNormalized: 1
  },
  {
    unique: true,
    partialFilterExpression: {
      deletedAt: {
        $exists: false
      }
    }
  }
)

Supports unique email among non-deleted users.

Query must align with partial predicate to use index appropriately.

Sparse index

Sparse excludes documents lacking field.

Partial indexes are often more expressive.

Understand semantics before using.

Sparse unique has surprising missing/null behavior.

TTL index

Automatically removes documents after time.

Example sessions:

javascript
db.sessions.createIndex(
  {
    expiresAt: 1
  },
  {
    expireAfterSeconds: 0
  }
)

TTL deletion is asynchronous/background, not exact-at-millisecond expiration.

Application must still check expiration:

text
if expiresAt <= now → invalid

Do not use TTL timing as security access boundary.

Multikey indexes

Indexing array field creates multikey index.

javascript
db.tasks.createIndex({
  tags: 1
})

Each array element contributes index entries.

Large arrays increase index size.

Compound indexes have restrictions when multiple array fields are involved.

Review current Mongo docs for multikey constraints.

Embedded fields

javascript
db.users.createIndex({
  "profile.city": 1
})

Supports nested field queries.

Covered query

If query and projection can be answered using index keys without fetching full document, query can be covered in eligible scenarios.

Example index:

javascript
{
  tenantId: 1,
  status: 1,
  title: 1
}

Projection only those fields (and _id handling).

Use explain to confirm.

Do not distort schema/index only to chase covered query unless measured benefit.

`explain`

javascript
db.tasks
  .find({
    tenantId,
    status: "open"
  })
  .sort({
    createdAt: -1
  })
  .explain("executionStats")

Inspect concepts:

text
winningPlan
IXSCAN
COLLSCAN
FETCH
SORT
totalKeysExamined
totalDocsExamined
nReturned
executionTimeMillis

Exact fields can vary with engine/version.

COLLSCAN

Collection scan.

Not automatically bad:

tiny collection;
admin one-off query;
query returns most documents.

But public frequent filtered query scanning millions is usually problem.

IXSCAN

Index scan.

Still can be inefficient if examines millions keys to return 3 documents.

Look at ratio:

text
keys examined
docs examined
returned

Blocking sort

Explain can reveal sort stage not satisfied by index.

Large sort can use memory/disk according to operation/configuration.

Better index can remove sort cost.

Selectivity

Field:

text
completed: true/false

has low cardinality.

Index only { completed: 1 } may be weak on huge dataset.

Compound:

javascript
{
  tenantId: 1,
  completed: 1,
  createdAt: -1
}

may be useful because tenant + status + sort match query.

Index intersection

MongoDB query planner can sometimes combine indexes.

Do not rely on intersection as replacement for a well-designed compound index for critical query.

Measure winning plan.

Text index

MongoDB text indexes support basic text search.

Only one text index per collection under classic text-index constraints.

For advanced relevance/autocomplete/fuzzy search, Atlas Search provides richer search indexing.

Do not confuse database B-tree-like indexes with Atlas Search indexes.

Atlas Search index

Atlas Search uses separate Lucene-based search infrastructure.

Supports:

full-text;
autocomplete;
fuzzy;
facets;
relevance.

Query through $search pipeline stage.

Search index is not ordinary createIndex.

Advanced lesson covers.

Geospatial

2dsphere

GeoJSON location:

javascript
{
  location: {
    type: "Point",
    coordinates: [
      78.1198,
      9.9252
    ]
  }
}

Index:

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

Coordinates order:

text
longitude, latitude

not latitude, longitude.

Use geospatial operators.

TTL + partial + unique design

One collection may have several indexes each serving different patterns.

Do not make one giant 12-field compound index hoping it solves all queries.

Index naming

Mongo creates names automatically, but explicit names can help migrations/ops:

javascript
{
  name: "tenant_status_createdAt"
}

Use stable naming conventions.

Hidden indexes

MongoDB supports hidden indexes to test impact of removing an index without dropping immediately in supported versions.

Useful for index cleanup.

Operational team can hide, observe planner/workload, then drop.

Index builds

Creating index on large production collection consumes resources.

Modern Mongo builds are designed for online operation but still affect CPU/disk/replication.

Plan rollout.

Over-indexing

Collection with 25 indexes:

Every insert/update may touch many index structures.

Symptoms:

slow writes;
large storage;
cache pressure.

Audit unused indexes.

Index statistics

Mongo can expose index usage stats via aggregation stage such as $indexStats.

Usage stats are not absolute proof an index is useless—rare critical queries may need it.

Combine app knowledge.

Query plan cache

MongoDB caches query plans.

Changes in data distribution/indexes can affect plans.

Advanced tuning may involve plan cache diagnostics.

Do not manually clear plan cache as first performance fix.

Common mistakes

index every field;
no compound index for actual filter+sort;
wrong order;
low-cardinality single-field index assumed magic;
unique enforced only in app;
TTL treated exact;
multikey array explosion;
deep pagination without matching index;
IXSCAN assumed fast without examined counts;
text index confused with Atlas Search;
production index build with no capacity plan.

Exercises

Create single/compound index for task query.
Use explain before/after.
Compare totalDocsExamined/nReturned.
Build tenant-scoped unique email index.
Add partial unique soft-delete index.
Add TTL session index and test delayed removal.
Index tags and inspect multikey.
Create geo 2dsphere query.
Find blocking sort and fix.
Audit a collection with intentionally excessive indexes.

Mastery checklist

Explain:

index costs;
compound order/prefix;
ESR;
unique;
partial/sparse;
TTL;
multikey;
text/Atlas Search;
geospatial;
explain;
IXSCAN/COLLSCAN;
covered query;
over-indexing.

137: MongoDB Indexes and Query Plans — Compound, Multikey, Unique, TTL, Text, Geospatial, Partial, and `explain()`

Learning objectives

Why indexes matter

Default _id

Single-field index

Compound index

Index prefixes

Equality, Sort, Range (ESR)

Sort direction

Range

Unique index

Case-insensitive uniqueness

Partial index

Sparse index

TTL index

Multikey indexes

Embedded fields

Covered query

explain

COLLSCAN

IXSCAN

Blocking sort

Selectivity

Index intersection

Text index

Atlas Search index

Geospatial

2dsphere

TTL + partial + unique design

Index naming

Hidden indexes

Index builds

Over-indexing

Index statistics

Query plan cache

Common mistakes

Exercises

Mastery checklist

Official references

Default `_id`

`explain`