Reference · Cheat sheet

Composite indexes & the leftmost-prefix rule

The one-page compressed essence: a composite index is a phone book — only leftmost prefixes are searchable.

Lesson: 0002Context: InnoDB, MySQL 8.0+

The one idea

Leftmost-prefix rule

Index (a, b, c) is sorted by a, then b, then c. It can seek on (a), (a,b), or (a,b,c) — never on anything that skips a.

Usable vs not — index (a, b, c)

WHERE	Seek?	Why
`a = 1`	yes	Prefix `(a)`.
`a = 1 AND b = 2`	yes	Prefix `(a, b)`.
`a = 1 AND b = 2 AND c = 3`	yes	Full key.
`a = 1 AND c = 3`	partial	Seeks on `a`; `c` only filtered (gap at `b`).
`b = 2` / `c = 3`	no	Skips leading `a` — full scan.
`a = 1 OR b = 2`	no	`OR` across columns breaks the prefix.

The range rule

A range stops the prefix

After the first > < BETWEEN LIKE 'x%' column, no later column is used for the seek. So order columns: equality first, range last. WHERE a=1 AND b>5 AND c=9 on (a,b,c) seeks a,b; c is just filtered.

Column-order recipe

1. columns used with = (equality) ← put first 2. the most selective of those ← earlier still 3. one range column (> < BETWEEN LIKE) ← put last 4. columns only in ORDER BY ← after the = columns, matching direction 5. columns only SELECTed (to cover) ← append to make it covering

Two things a composite index gives free

Replaces single-column indexes on its prefixes — one index on (a,b,c) serves (a) and (a,b) lookups too.
Kills a filesort — an ORDER BY matching a leftmost prefix (same direction) reads the index in order. No Using filesort.

Read it in EXPLAIN

Signal	Meaning
`key: name`	The composite index was chosen.
`key: NULL`, `type: ALL`	No prefix matched → full table scan.
`Using index`	Covering — answered from the index alone.
`Using filesort`	Index didn't supply order → separate sort.
`Using index condition`	Index Condition Pushdown filtered non-prefix parts.

Source: MySQL 8.0 Manual — Multiple-Column Indexes · ORDER BY Optimization · All lessons