Lesson 0002 · Internals & Performance

Composite indexes & the leftmost-prefix rule

One index on several columns is a phone book, not a set of separate lookups — and that single fact decides which of your queries it can actually help.

~9 minRetrieval quiz + hands-on EXPLAINCheat sheet: here

In Lesson 0001 you learned that a secondary index is its own B+tree, ordered by the columns you indexed. This lesson asks the next question: what happens when you index several columns at once — an index on (a, b, c)? The answer, the leftmost-prefix rule, is the single most useful thing to know when deciding which index to build and in what column order.

The one idea: it's sorted like a phone book

A composite index on (last_name, first_name) is sorted first by last_name, and only then by first_name within each last name — exactly like a phone book. That ordering is the whole story.

You can find someone if you know their last name (jump to the Joneses), or their last name and first name (jump to "Jones, John"). But you cannot use this phone book to find everyone whose first name is "John" — those are scattered across every last name. The index can't help.

Which queries use index (last_name, first_name)?

Straight from the manual's own example:

— MySQL Manual — Multiple-Column Indexes

Three consequences you can design around

1. Column order is a design decision

Because only leftmost prefixes work, the order of columns in the index is not cosmetic. Put the column you'll always filter on first. A rough rule: columns used in equality (=) filters go before columns used in ranges, and the most selective equality column tends to go first.

2. One composite index replaces several single-column ones

An index on (a, b, c) already gives you indexed lookups on (a) and (a, b) for free — so you usually don't also need separate indexes on a or on (a, b). Fewer indexes means cheaper writes and less space (recall from 0001: every index copies the PK into every entry).

3. The index can also do your ORDER BY — for free

Because the index is physically sorted, an ORDER BY that matches a leftmost prefix of the index (in the same direction) is satisfied by reading the index in order — no separate sort. If it can't, EXPLAIN shows Using filesort: MySQL had to sort the rows itself. — MySQL Manual: "If the Extra column … contains Using filesort, … a filesort is performed."

Check yourself

Answer from memory — effortful recall is what builds retention. Feedback is immediate.

Hands-on: watch the prefix rule decide the plan

You have MySQL ready. Build one composite index and read what the optimizer does.

1 · A table with a composite index

CREATE TABLE people (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  last_name  VARCHAR(60) NOT NULL,
  first_name VARCHAR(60) NOT NULL,
  city       VARCHAR(60) NOT NULL,
  INDEX name (last_name, first_name)     -- composite
);
INSERT INTO people (last_name, first_name, city) VALUES
  ('Jones','Ann','Leeds'), ('Jones','John','Bath'), ('Jones','Jon','York'),
  ('Smith','Al','Hull'),   ('Smith','Zoe','Ely');

2 · Predict, then run each — read the key and Extra columns

-- A) leftmost prefix → should use key 'name'
EXPLAIN SELECT * FROM people WHERE last_name = 'Jones';

-- B) skips the leading column → key should be NULL (full scan)
EXPLAIN SELECT * FROM people WHERE first_name = 'John';

-- C) ORDER BY along the index → NO 'Using filesort'
EXPLAIN SELECT * FROM people WHERE last_name='Jones' ORDER BY first_name;

-- D) ORDER BY off the index → expect 'Using filesort'
EXPLAIN SELECT * FROM people ORDER BY city;