MongoDB Compound Indexes Must Match Filtering and Sort

A compound MongoDB index orders documents by a sequence of fields. Its usefulness depends on query predicates, sort order and which leading fields are constrained. Indexing every field separately does not automatically produce the same access path.

Use the equality-sort-range guideline as a starting hypothesis, then inspect execution statistics. Selectivity and workload tradeoffs can justify a different order; the guideline is not a substitute for measurement.

The concrete problem

Consider a ticket list that filters by tenant and status and sorts newest-first. Use a current supported MongoDB deployment and check its exact version before comparing planner behavior. The _id tie-breaker makes ordering deterministic when timestamps match.

Figure: Tenant and status equality → Ordered index segment → Limited deterministic results.

A worked example

db.tickets.createIndex({
  tenantId: 1, status: 1, createdAt: -1, _id: -1
});
db.tickets.find({tenantId: 't42', status: 'open'})
  .sort({createdAt: -1, _id: -1})
  .limit(50)
  .explain('executionStats');

Why the behavior matters

The equality conditions identify the relevant tenant/status segment, while the remaining fields provide the desired order. Inspect totalKeysExamined, totalDocsExamined, returned rows and sort stages. Execution statistics execute the query, so use a safe query and appropriate environment.

Now remove the status filter. The same index groups records by status before timestamp, which can change its ability to satisfy the requested timestamp ordering directly. Add a range predicate and reassess the relationship between sort support and range selectivity. One successful plan does not prove the index is ideal for every endpoint using the collection.

Failure modes and design tradeoffs

Projection coverage has additional requirements, including how _id and indexed fields are selected. Multikey indexes introduce constraints when indexed fields contain arrays. Collation must also match the intended comparison behavior. Document these schema properties rather than treating a collection as untyped by definition.

An index increases storage and write maintenance. Redundant prefixes may sometimes be removed, but uniqueness, sparse/partial options and actual workload usage can make apparently similar indexes non-equivalent. Review before deleting anything.

What to verify before adoption

Generate skewed tenants and realistic status proportions, then test the main query and important variants. Compare cold and warm runs separately. Include tied timestamps and pagination. Report plan evidence and workload assumptions; do not claim a speedup from an index name or a hypothetical screenshot.

Primary reference

• MongoDB documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.