Skip to content

CONCEPT Cited by 1 source

Leftmost prefix rule

The leftmost-prefix rule states that a composite index on columns (A, B, C, …) can answer only those queries whose WHERE clause filters on a contiguous leading prefix of the index's column list: A, (A, B), (A, B, C), … Queries filtering on B alone, C alone, or (B, C) — any subset that skips a column or starts from the middle — cannot use the index at all, and MySQL will not even include it in possible_keys.

(Source: sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index.)

The rule in one sentence

MySQL will only be able to use the columns starting on the left and working toward the right. It cannot skip any columns.

— Aaron Francis, Why isn't MySQL using my index?

PlanetScale's framing refers to the MySQL manual which uses the term "leftmost prefix" as the canonical vocabulary.

Why the B+tree shape dictates this

A composite index is a single B+tree keyed on the tuple (A, B, C), sorted lexicographically: first by A, then within each A value by B, then within each (A, B) pair by C.

For the planner to seek into the tree, it needs to know the leading column values — that's how B+tree navigation works (compare against the root, descend to the matching child page, recurse). With a predicate on B but not A, there is no way to seek — every value of A is a potential starting point, so the planner would have to scan the whole tree anyway. At that point it's usually cheaper to just scan the (clustered-index) table itself.

Francis's worked example

Schema:

ALTER TABLE people DROP INDEX first_name;
ALTER TABLE people DROP INDEX state;
ALTER TABLE people ADD INDEX multi (first_name, state);

Query 1 — uses the index (both leading columns filtered):

EXPLAIN SELECT * FROM people
  WHERE first_name = 'Aaron' AND state = 'TX';

-- | type | key   | key_len | ref         | rows | Extra |
-- | ref  | multi | 210     | const,const |  178 |       |

Query 2 — does not use the index (skips leading column):

EXPLAIN SELECT * FROM people WHERE state = 'TX';

-- | type | key  | rows   | Extra       |
-- | ALL  | NULL | 493889 | Using where |

With no first_name predicate, the multi composite index cannot be seeked and is not considered — possible_keys is empty. MySQL falls back to a full table scan (type: ALL, 493,889 rows).

The partial-prefix win

You do not have to use every column of a composite index — only the leading contiguous prefix:

-- Uses `multi` via the leading `first_name` alone:
SELECT * FROM people WHERE first_name = 'Aaron';
-- type: ref, key: multi, key_len: 202 (just the first_name bytes)

The key_len shrinks to reflect how much of the composite key was used for the lookup. This behaviour is what makes composite-index column ordering a design decision: put the columns that are always filtered first, and the columns that are sometimes filtered later.

Design implications

  1. Put high-frequency filter columns leftmost. If every query filters on tenant_id and some filter on tenant_id + user_id, index (tenant_id, user_id) — never (user_id, tenant_id).
  2. Equality before range. Leading-column equality narrows the B+tree subtree before a trailing range scan; leading-column range means the trailing equality still scans a wide region.
  3. Don't expect column-skip to work. Index (A, B, C) + predicate (A, C) uses only the A portion — the C predicate applies post-fetch.
  4. Two separate single-column indexes are not a composite. MySQL's index merge optimisation can sometimes combine single-column indexes, but patterns/composite-index-for-and-predicate covers the AND-predicate case more cleanly with a single composite index.

EXPLAIN diagnostic

EXPLAIN surfaces the rule via:

  • possible_keys — if the composite index isn't here, your predicate doesn't form a leftmost prefix.
  • key_len — reveals how much of the composite key the planner actually used. A value matching only the first column's byte width means only the leading column is doing work.

Seen in

Last updated · 470 distilled / 1,213 read