Missing compound index

MongoDB · Query & indexes · L1

Prototype — data is mocked and actions are not connected yet.

← Query performance across the stack

MongoDB Incident Query & indexes · L1 In progress

A find() with a filter and a sort does a full collection scan because no compound index supports it.

Symptoms

A list endpoint times out as the collection grows. explain() reports a COLLSCAN with a high totalDocsExamined, far above the number of documents returned.

Root cause

The query filters on an equality field and sorts on another, but there's no compound index covering both, so MongoDB scans every document and sorts in memory (risking the 32MB sort limit).

What you should do

  1. Run the query with .explain('executionStats') and confirm the COLLSCAN.
  2. Note the equality fields, the sort field and any range filters.
  3. Create a compound index following ESR: Equality, then Sort, then Range.
  4. Re-run explain() and confirm an IXSCAN with totalDocsExamined ≈ returned.

How it's simulated

Seed a collection of several million documents and a query that filters by status and sorts by createdAt, with the supporting compound index dropped.

Scoring

  • DetectRead explain() and recognised the collection scan and in-memory sort.
  • FixCreated a compound index in ESR order that turns the scan into an index scan.
  • TrapDidn't put the sort field before the equality field and break index usage.
Back to goal

Prototype — scenario content is a preview; the live sandbox is not wired up.