Indexes

Article · 3:00

What an index is, the leftmost prefix rule, and the indexes that are never used.

An index is a second copy of one or more columns, kept sorted, with a pointer back to the row. Finding a value becomes a descent through a B-tree - a handful of page reads - instead of a scan of every row. That is the whole mechanism, and most index advice follows from it.

Because the copy is sorted by the first column first, a composite index on (surname, forename, city) can serve a query filtering on surname, or on surname and forename, or all three. It cannot serve one filtering only on forename, any more than a phone book sorted by surname helps you find everyone called Ada. This is the leftmost prefix rule, and it is the most useful single fact about indexing.

The same sorting means an index can satisfy an ORDER BY without sorting, and a range scan - BETWEEN, >, LIKE 'abc%' - but not a leading wildcard, because LIKE '%abc' has no prefix to seek to.

Indexes that do not pay their way: one on a low-cardinality column such as a two-value flag, where the optimiser will scan instead because half the table matches; a duplicate of the leftmost prefix of an existing composite index; and any index on a write-heavy table that no query uses, since every index must be updated on every insert, update and delete. Indexes are not free - they are a trade of write speed and disk for read speed.

CREATE INDEX idx_people_name ON people (surname, forename, city);

-- Uses the index: the filter starts at the leftmost column.
SELECT * FROM people WHERE surname = 'Lovelace';
SELECT * FROM people WHERE surname = 'Lovelace' AND forename = 'Ada';

-- Cannot use it. A phone book sorted by surname does not help you find
-- everyone called Ada.
SELECT * FROM people WHERE forename = 'Ada';
SELECT * FROM people WHERE city = 'London';

-- Ranges work, because the copy is sorted.
SELECT * FROM orders WHERE placed_at BETWEEN '2026-01-01' AND '2026-01-31';
SELECT * FROM people WHERE surname LIKE 'Lov%';     -- prefix: a seek
SELECT * FROM people WHERE surname LIKE '%lace';    -- no prefix: a scan

-- Sorting for free: the index is already in this order.
SELECT id FROM orders WHERE customer_id = 7 ORDER BY placed_at DESC;

-- Indexes that do not pay for themselves:
--   an almost-constant column - the optimiser scans anyway
-- CREATE INDEX idx_products_active ON products (is_active);
--   a duplicate of a leftmost prefix that already exists
-- CREATE INDEX idx_people_surname ON people (surname);

-- What exists, and how selective it is. Cardinality near the row count
-- is a useful index; near 1 is not.
SHOW INDEX FROM people;

-- Indexes nothing has used since the server started.
SELECT * FROM sys.schema_unused_indexes;