Part 3 of 5 · 1 min read
Column order determines which queries it helps
The leading column must match the query's filter for the index to be usable at all.
Think of the phone-book sorted by last-name-then-first-name again. Looking up "every Smith" is easy. Looking up "everyone whose first name is Anna," regardless of last name, gets no help at all, since first names are scattered across the whole book.
In plain words: a composite index can only be used efficiently starting from its leading, leftmost column. A query that filters on the leading column, or on the leading column plus later columns in order, can use the index. A query that filters only on a later column, skipping the leading one, cannot use the index efficiently at all, because that column's values are not grouped together across the whole index; they are only sorted within each leading-column group. Choosing column order correctly, based on the most common query patterns, is one of the most important decisions when designing a composite index.
A worked example. The (customer_id, placed_on) index helps WHERE customer_id = 42, but a query filtering only WHERE placed_on = '2026-01-01', with no customer_id filter, cannot use this index efficiently, since dates are only sorted within each customer's group, not across the whole table.
The key lines.
SELECT * FROM orders WHERE placed_on = '2026-01-01';
-- the (customer_id, placed_on) index gives little help here: placed_on is not the leading column
Leading column
The leading column of a composite index is its first, leftmost column; the index can only be used efficiently for queries that filter on this column, alone or together with later columns in order.
Example: last name is the leading column of the last-name-then-first-name phone book; searching by first name alone gets no benefit from that ordering.