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

Learning objectives

By the end of this lesson, you should be able to:

explain how indexes trade storage and write cost for faster reads;
create single-field and compound indexes;
reason about index prefixes and field order;
apply Equality-Sort-Range thinking to an index design;
explain what changes when an indexed field contains an array;
enforce uniqueness at the database boundary;
choose partial, sparse, and TTL indexes appropriately;
distinguish text indexes from geospatial indexes, wildcard indexes, and Atlas Search;
read the practical parts of an explain() result;
recognize the difference between COLLSCAN and IXSCAN;
reason about covered queries; and
avoid paying for indexes that do not support a real workload.

Why indexes matter

Suppose MongoDB receives a query but has no useful way to narrow the search. It may need to inspect many documents, potentially the entire collection, before it knows which ones match:

find matching documents
→ inspect many/all documents

An appropriate index gives the server an additional structure it can navigate instead of repeatedly examining unrelated documents:

navigate index
→ locate matching records

That speedup is not free. An index is an additional data structure, and MongoDB has to maintain it whenever indexed data changes. The practical costs include:

disk space;
memory and cache pressure;
extra work for inserts, updates, and deletes; and
operational complexity when indexes are built, monitored, changed, or removed.

The useful distinction is that an index is a workload decision, not a decoration for every field. Do not add indexes to every field just because queries might eventually use them.

The index does not replace the collection. It is a second representation of selected field values and references to the documents that contain them. MongoDB chooses whether that representation is worth using for a particular query.

That choice depends on the query shape and the data, not just on whether an index exists. A query that returns nearly every document may be cheaper as a collection scan, while a selective query can benefit greatly from an index.

Default `_id`

MongoDB automatically creates a unique index for the _id field. That makes a lookup by _id efficient in the normal case.

There is a separate security boundary here that is easy to miss: an efficient ID lookup is not authorization. A request that can efficiently find document X still needs an authorization check proving that the caller may read or change document X.

Single-field index

A single-field index is often the smallest useful index for a query that filters on one field:

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

For equality on a lone field, the direction usually matters less. The distinction becomes more important when this field participates in a compound index and the query also sorts results.

For a single equality predicate, ascending and descending order can usually locate the same set of values. That does not mean the two directions are interchangeable once the field is part of a compound ordering.

Compound index

Most useful application queries filter, sort, and limit together. For example, this query looks for open tasks belonging to a tenant, returns newest tasks first, and limits the page size:

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

A candidate compound index is:

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

This ordering gives the planner a path for the tenant equality, the status equality, the requested sort, and the _id pagination tiebreaker. The final field is especially useful when multiple records have the same createdAt value, because a stable unique ordering prevents ambiguous page boundaries.

The index is a candidate, not a guarantee that it is the best choice for every data distribution. Use the actual query and explain() to verify it.

Index prefixes

Consider this index:

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

Its useful leading prefixes are:

tenantId
tenantId + status
tenantId + status + createdAt

That means it can support queries that begin with those fields, subject to the details of the predicate and sort. A query only on:

status

cannot generally use this compound index as effectively as it could if status were the leading field. The server may still have options in particular cases, but the index was not ordered around a status-only access pattern.

Design indexes from real filter and sort patterns. Do not assume that adding fields to one large index automatically covers unrelated queries.

It is normal for one application to need several focused compound indexes. The right set is the smallest set that serves important query shapes while keeping write and storage costs acceptable.

Equality, Sort, Range (ESR)

When deciding how to order fields in a compound index, a useful starting guideline is:

equality fields
then sort fields
then range fields

For example, a query might have these requirements:

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

An index that reflects that shape is:

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

The equality fields narrow the working set first. The sort fields then help produce the requested order, while the range is used for cursor-style pagination. This is a design heuristic, not a law that replaces measurement. Real query-planner behavior can vary with the query, available indexes, and data distribution, so verify the result with explain().

ESR is most useful during the first design pass, when it helps turn a query into an explicit ordering decision. The execution plan is the final check because the planner evaluates the complete query and the available data.

Sort direction

Compound indexes can support particular forward and reverse order combinations. The direction of each field matters when several sort fields must be satisfied together.

For example, if the query sorts by both fields in descending order:

{
  createdAt: -1,
  _id: -1
}

the index should match that direction for the intended access pattern. Mixed directions matter too; an index that supports { createdAt: -1, _id: -1 } is not interchangeable with every possible combination of ascending and descending fields.

Test the actual query with explain() rather than inferring performance from the index definition alone.

Range

A range predicate may look like this:

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

Once the planner reaches a range field in an index, later fields may have less ability to contribute to filtering or sorting, depending on the complete query. This is why field order matters: think through which fields narrow equality, which fields provide ordering, and where the range begins.

This does not mean range queries are wrong or that every range field must be last in every index. It means you should be clear about which later fields you expect the index to support and confirm that expectation with execution statistics.

Unique index

If email addresses must be unique within each tenant, enforce that rule with a compound unique index:

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

Because the database enforces the constraint, this remains correct when two requests arrive concurrently. A pre-check in application code is useful for a friendly response, but it cannot establish uniqueness by itself:

SELECT/find existing first
then insert

Two requests can both observe that the value is free before either insert completes. The database unique constraint or index is the authority. Catch the duplicate-key error and map it to an appropriate application response such as 409 or 422.

The exact response mapping belongs to the API contract, but the database error should not be silently treated as a generic server failure. The client needs a clear indication that the requested value conflicts with an existing record.

That response is still based on a database-enforced fact, not on the result of the earlier pre-check. The insert or update must be allowed to race safely and must handle the constraint failure.

Case-insensitive uniqueness

Naively lowercasing a value is not always enough when Unicode, email, or domain-specific normalization rules are involved. Make the normalization policy explicit.

Common options include:

storing a deliberately normalized field; or
using a collation-aware index.

Collation affects comparison, sorting, and whether a query matches an index. Decide what equivalence means for the application before choosing the index; “case-insensitive” is not a complete specification for every language and identifier format.

For example, decide explicitly how a missing email, an explicit null, and an empty string should behave. The index cannot express an application policy that was never defined.

Partial index

A partial index includes only documents that match a specified expression. This is useful when a rule applies to an active subset of a collection.

For example, the following index enforces unique email addresses only for users that do not have a deletedAt field:

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

This supports unique email among non-deleted users while allowing a deleted record and a current record to share the same email. Queries also need to align with the partial predicate for the index to be usable appropriately. If a query does not establish that it is operating within the indexed subset, MongoDB may not be able to rely on the partial index for the requested result.

Sparse index

A sparse index excludes documents that lack the indexed field. Partial indexes are often more expressive because their filter can describe an explicit condition rather than only field presence.

Do not choose sparse behavior without checking its exact semantics. In particular, sparse unique indexes can produce surprising behavior around missing and null values. Test the cases your schema permits before making the index a production constraint.

TTL index

A TTL index removes documents after a time-based condition. For sessions whose expiresAt value is an absolute expiration time, a common definition is:

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

TTL deletion is asynchronous and happens in the background. It is not an exact-at-the-millisecond expiration mechanism.

The application must still validate expiration when it handles a session:

if expiresAt <= now → invalid

Do not use the timing of TTL deletion as the security access boundary. A session must be rejected because its expiration has passed, even if its document has not yet been removed.

TTL is therefore a cleanup mechanism as well as a storage-management tool. It keeps expired records from accumulating indefinitely, but request-time validation remains responsible for correctness and security.

If expiration has business consequences beyond session cleanup, record and test those rules in the application as well. A background deletion process should not be the only place where the expiration policy exists.

Multikey indexes

Indexing an array field creates a multikey index. For example:

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

MongoDB creates index entries based on the array elements, allowing queries to match individual tags. The trade-off is that a large array can contribute many entries and increase index size and write work.

Compound indexes also have restrictions when multiple array fields are involved. The exact constraints matter when the schema contains more than one array path, so review the current MongoDB documentation for multikey limitations before deploying such an index.

Embedded fields

Nested fields can be indexed by using their dotted path:

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

This supports queries against the nested profile.city value. The path in the query and the path in the index need to describe the same data shape.

The same dotted-path convention is used when querying the field. Keeping the stored shape and query shape consistent makes both the index definition and the resulting plan easier to inspect.

Indexing a nested path does not flatten arbitrary application logic. If the field is absent, has a different type, or is embedded in a different structure, the query still needs to account for those document shapes.

Covered query

In eligible scenarios, MongoDB can answer both the predicate and the projection from index keys without fetching the full documents. This is called a covered query.

For example, an index containing these fields may cover a query that filters and projects only them:

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

Projection details, including handling of _id, matter. Use explain() to confirm whether the query is actually covered rather than assuming that the index definition is sufficient.

Do not distort the schema or add awkward indexes solely to chase a covered query unless measurement shows that the benefit justifies the extra storage and write cost.

Coverage is an optimization, not a requirement for a healthy query. First make the access pattern selective and correctly ordered; only then consider whether avoiding document fetches produces a meaningful measured improvement.

`explain`

Run the query with execution statistics when you need to understand what the server actually did:

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

At a practical level, inspect these concepts:

winningPlan
IXSCAN
COLLSCAN
FETCH
SORT
totalKeysExamined
totalDocsExamined
nReturned
executionTimeMillis

The exact field names and plan-tree shape can vary with the MongoDB engine and version. Read the plan as evidence: identify the winning strategy, determine whether documents or index keys were examined, check whether a fetch or blocking sort occurred, and compare the examined counts with the number returned.

Compare plans under representative data. A plan that looks good in a small local collection may behave differently once tenants, statuses, timestamps, and array sizes have the production distribution.

COLLSCAN

COLLSCAN means collection scan. The server is scanning collection documents rather than starting from a useful index range.

That is not automatically a defect. It may be reasonable for:

a tiny collection;
an occasional administrative query; or
a query that returns most documents anyway.

For a frequent public query that filters a collection containing millions of documents, however, a collection scan is usually a problem worth investigating.

IXSCAN

IXSCAN means index scan, but seeing that stage does not automatically mean the query is efficient. An index scan can still examine millions of keys to return only three documents.

Look at the relationship among:

keys examined
docs examined
returned

This ratio tells you more than the stage name alone. A well-targeted plan generally avoids examining a much larger set than it needs to return, although the acceptable ratio depends on the query and workload.

Use the same query shape when comparing plans. Changing predicates, projections, sort order, or limits can cause MongoDB to select a different plan, so comparisons are meaningful only when the workload being measured is held constant.

Blocking sort

An explain() result can show a sort stage when the chosen index does not satisfy the requested order. That sort is often called a blocking sort because the server must collect enough results before it can produce the final ordered output.

Large sorts can consume memory and may use disk according to the operation and configuration. A better index can remove that sort cost, but it should still be designed around the complete filter and sort pattern rather than added blindly.

When diagnosing this case, inspect both the sort stage and the number of documents reaching it. Sorting a narrowly filtered set is a different operational problem from sorting a large collection after a broad scan.

Selectivity

A field with only two possible values has low cardinality. For example:

completed: true/false

An index containing only { completed: 1 } may be weak on a very large collection because either value can match a substantial fraction of the documents.

A compound index can become useful when it matches the rest of the query shape:

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

The combination of tenant, completion status, and sort order may narrow and order the data effectively even though completed alone is not selective.

Index intersection

MongoDB's query planner can sometimes combine multiple indexes for one query. This is index intersection.

Do not depend on intersection as a replacement for a well-designed compound index for a critical query. Measure the winning plan and its execution statistics, especially when the query is frequent or latency-sensitive.

Intersection can be useful, but it may require combining and comparing results from separate structures. For a high-volume query, an intentional compound index is usually easier to reason about and validate.

Text index

MongoDB text indexes provide basic text search. Under classic text-index constraints, a collection can have only one text index.

For advanced relevance, autocomplete, or fuzzy-search requirements, Atlas Search provides a richer search-indexing system. Do not confuse the database's B-tree-like indexes with Atlas Search indexes; they are different mechanisms with different query capabilities and operational behavior.

Atlas Search index

Atlas Search uses separate Lucene-based search infrastructure. It supports features such as:

full-text search;
autocomplete;
fuzzy matching;
facets; and
relevance scoring.

Queries use the $search aggregation-pipeline stage. A search index is not created with ordinary createIndex, and an ordinary MongoDB index is not a substitute for an Atlas Search index when those search features are required.

Advanced material covers the broader Atlas Search workflow.

Keep the distinction visible in architecture discussions: ordinary indexes support MongoDB query planning, whereas Atlas Search maintains a separate search-oriented index and is queried through its search operators.

Geospatial

2dsphere

For Earth-like geographic coordinates, store a location as GeoJSON. A point can look like this:

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

Create a 2dsphere index for that field:

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

The coordinate order is a frequent source of bugs:

longitude, latitude

It is not latitude followed by longitude. Use MongoDB's geospatial operators with the indexed GeoJSON field, and validate incoming coordinates before storing them.

A field can be syntactically valid GeoJSON and still contain the wrong place if the coordinate order is reversed. Validation should check both the GeoJSON shape and the allowed longitude and latitude ranges.

TTL + partial + unique design

A collection can have several indexes, each supporting a different access pattern or data rule. For example, TTL may handle session cleanup, a partial unique index may enforce active-user uniqueness, and a compound index may serve a tenant's task list.

Do not create one giant 12-field compound index in the hope that it will solve every query. Large indexes cost more to maintain and still do not necessarily provide the right prefix, range, or sort behavior.

Index naming

MongoDB generates index names automatically, but explicit names make migrations and operational work easier to reason about:

{
  name: "tenant_status_createdAt"
}

Use stable naming conventions so an index can be identified consistently in deployment scripts, monitoring, and cleanup work.

Names also make a migration review easier: an operator can tell which query pattern an index was intended to serve without reconstructing the purpose from an automatically generated field list.

Hidden indexes

In supported MongoDB versions, hidden indexes let you test the effect of removing an index without dropping it immediately. This is useful during index cleanup because the definition remains available if the workload shows that it is still needed.

An operations team can hide an index, observe the planner and workload, and then drop it once the evidence supports removal.

Observation still needs to cover the relevant workload window. A short test period may miss a scheduled report, a rare support operation, or a traffic pattern that occurs only at a particular time.

Index builds

Building an index on a large production collection consumes resources. Modern MongoDB index builds are designed for online operation, but they still affect CPU, disk, and replication capacity.

Plan the rollout: account for the collection size, available resources, replication behavior, and expected workload rather than treating index creation as a cost-free metadata change.

The safe rollout procedure depends on the deployment topology and MongoDB version. Coordinate the build with the team responsible for capacity, replication lag, and rollback decisions.

Over-indexing

Consider a collection with 25 indexes. Every insert or update that changes indexed data may need to touch many index structures. Typical symptoms include:

slower writes;
increased storage use; and
cache pressure.

Audit unused indexes, but interpret the evidence with the workload in mind. An index that appears quiet may still support an infrequent but critical operation.

Index statistics

MongoDB can expose index usage statistics through an aggregation stage such as $indexStats.

Usage statistics are evidence, not absolute proof that an index is useless. A rare operational or recovery query may still depend on it. Combine the statistics with knowledge of the application, scheduled jobs, administrative procedures, and production requirements before removing anything.

An index review is therefore an ongoing operational practice, not a one-time cleanup task. Revisit decisions when query shapes, traffic, or data distribution changes.

Query plan cache

MongoDB caches query plans. As data distribution and available indexes change, the best plan for a query can change as well.

Advanced performance work may involve plan-cache diagnostics. Manually clearing the plan cache should not be the first performance fix; first verify the query shape, indexes, data distribution, and execution statistics.

Changing the index or query shape can legitimately change the selected plan. Diagnose the cause of a poor plan before taking an operational action that only removes cached information temporarily.

Common mistakes

indexing every field without a workload-based reason;
failing to create a compound index for the actual filter-plus-sort pattern;
choosing the wrong field order;
assuming a low-cardinality single-field index is automatically effective;
enforcing uniqueness only with an application pre-check;
treating TTL removal as exact expiration;
overlooking index-entry growth for large arrays;
using deep pagination without a matching index and stable tiebreaker;
assuming IXSCAN is fast without checking examined counts;
confusing a MongoDB text index with Atlas Search; and
building an index in production without a capacity and rollout plan.

Exercises

Create a single-field and a compound index for the task query.
Use explain() before and after the index change.
Compare totalDocsExamined with nReturned.
Build a tenant-scoped unique email index.
Add a partial unique index for soft-deleted users.
Add a TTL session index and test that removal is delayed rather than exact.
Index tags and inspect the resulting multikey behavior.
Create a 2dsphere index and run a geospatial query.
Find a blocking sort and change the index to address it.
Audit a collection that intentionally contains excessive indexes.

When working through these exercises, record what explain() reports before and after each change. The goal is not merely to make an index exist; it is to connect the index definition to the plan, examined counts, write cost, and observed workload.

For the unique, partial, and TTL exercises, include both the expected success case and a failure or boundary case. For the geospatial exercise, verify coordinate order explicitly. These checks turn each index from a definition copied into the shell into a behavior you can explain.

The final audit should include both read behavior and write impact. An index that improves one measured query but materially harms the collection's write path may not be the right production trade-off.

Mastery checklist

You should be able to explain:

index costs;
compound index order and prefixes;
ESR;
unique indexes;
partial and sparse indexes;
TTL indexes;
multikey indexes;
text indexes and Atlas Search;
geospatial indexes;
explain();
IXSCAN and COLLSCAN;
covered queries; and
over-indexing.