Indexing & the ESR rule

The keystone

Indexes are B-trees. No index → collection scan (COLLSCAN).

_id index is auto-created, unique, and cannot be dropped.

Leftmost prefix (same as InnoDB)

A query can use only a prefix of a compound index's field order.

{ a, b, c } serves a · a,b · a,b,c — not b, c, or b,c alone.

ESR — order compound fields

Equality → Sort → Range.

E first: most selective; keeps the rest sorted. S before R: a range reads a span, so a sort after it falls back to an in-memory sort.

ERS exception: only when the range is very selective. Equality is always first.

Multikey (arrays) — the new twist

Index a field holding an array → auto multikey: one entry per element, all pointing to the same doc.

A compound index may contain at most one array field.

A multikey index cannot cover a query.

Covered query (= index-only scan)

All needed fields (filter + returned) are in the index → answered from the index, no document fetch.

Cousin of Postgres index-only scan / InnoDB covering index.

Worked ESR example

find({ dir: "X", runtime: {$lt:130} }).sort({ year: 1 })

→ index { dir:1, year:1, runtime:1 } — dir=E, year=S, runtime=R. IXSCAN, no SORT stage.