Indexes and Query Performance
Index types
Every collection has an _id index. Beyond that: single-field, compound, multikey (created automatically when you index an array field), text, wildcard, hashed, geospatial, TTL (single-field, date-typed, expires on a ~60-second background sweep), unique, sparse, and partial indexes.
Compound index rules
A compound index can serve a query only if the query uses a prefix of its keys, and it can only provide a sort if the sort keys follow the equality predicates. This is the ESR rule: Equality fields first, then Sort fields, then Range fields. Querying {status: 1, createdAt: -1} with an index on {status: 1, createdAt: -1} is covered by ESR; an index on {createdAt: 1, status: 1} cannot give a sorted result for that query.
Diagnosing
explain("executionStats") shows COLLSCAN versus IXSCAN, totalKeysExamined, totalDocsExamined, and nReturned. A healthy index keeps keys and docs examined close to documents returned. An in-memory sort shows SORT and is capped at 32 MB; exceeding it fails the query.
Traps
$regex without a left anchor (^) cannot use an index. A multikey index cannot be used for a covered query. You may not create an index on two array fields in the same compound index. Text indexes are limited to one per collection. Low-cardinality indexes on booleans rarely help. hint forces an index for testing only.
Rule of thumb
Build the index from the query shape, not the schema: list the equality predicates, then the sort, then the range predicate, and create the compound index in exactly that order.