Two lookups compared: without an index every row of the table is read, while an index descends three levels of a sorted tree to reach the row - and below, a composite index on three columns showing which WHERE clauses can use it and which cannot.

An index is a second copy of your data, sorted - and everything follows from that

Most people learn indexing as a list of rules.

Put an index on columns you filter by. Do not index a column with only two values. Composite indexes only work left to right. A function in the WHERE clause breaks the index. LIKE 'abc%' is fine but LIKE '%abc' is not. Index your foreign keys. Do not add too many.

Memorised, that list is seven unrelated facts and you will misremember three of them. They are not unrelated. They are one mechanism, seen from seven angles, and the mechanism takes a sentence:

An index is a second copy of some of your columns, kept sorted, with a pointer back to the row.

Every rule above is a consequence. Once you can see the sorted copy, you can derive them, and you stop needing the list.

Start with the phone book

A printed phone book is a table sorted by surname. Finding Lovelace is fast: open near the middle, see you are past it or short of it, halve the remaining pages, repeat. A few steps and you are there, out of a million entries.

Now find everyone whose first name is Ada.

You read the whole book. Not because the book is badly made, but because it is sorted by the wrong thing for that question. The data is all there; the order is not.

That is a full table scan, and it is also, exactly, why a composite index on (surname, forename) cannot answer a query that filters only on forename. People memorise this as "the leftmost prefix rule". It is really just the phone book.

What the database actually keeps

When you create an index, MySQL builds a B-tree: a sorted structure where each node holds a range of values and pointers to the nodes below. The leaves hold the indexed values in order, each with enough information to find the full row.

The tree is shallow. Each node is a 16 KB page holding hundreds of entries, so three levels index millions of rows and four index billions. That is the whole performance story: three or four page reads instead of a million.

CREATE INDEX idx_people_surname ON people (surname);

You now have two copies of surname. One in the table, in whatever order rows were inserted. One in the index, sorted. Writes maintain both, which is where the cost lives - every INSERT, UPDATE and DELETE updates every affected index. An index is a trade: write speed and disk, for read speed.

That trade explains the last rule on the list. Ten indexes on a write-heavy table means ten structures to update on every insert. If a query never uses one, you are paying the write cost for nothing - which is why sys.schema_unused_indexes exists.

Deriving the rules

"Do not index a column with two values." A sorted copy of is_active is a block of false and then a block of true. Seeking into it leaves half the table. The database then has to fetch half the rows individually, in index order, jumping around the table - which is slower than reading the table in order. The optimiser knows this and ignores your index. It was never about "low cardinality is bad"; it is that halving a million is still half a million, and random access is worse than sequential.

"A function breaks the index." The copy is sorted by placed_at. You asked for rows where YEAR(placed_at) = 2026. There is no sorted copy of YEAR(placed_at) - so there is nowhere to seek to, and the only way to evaluate the function is on every row. Rewrite it as a range on the column itself:

-- Cannot use the index: no sorted copy of YEAR(placed_at) exists.
WHERE YEAR(placed_at) = 2026

-- Uses it: a seek to the start, then a scan to the end.
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'

Same rows, same meaning, and often a hundred-fold difference. This one rewrite accounts for an enormous share of real-world query speed-ups.

"LIKE 'abc%' works, LIKE '%abc' does not." The copy is sorted alphabetically. Given a prefix, you can seek to where it starts and read until it ends. Given a suffix, there is no starting point - entries ending in "abc" are scattered throughout. The phone book again: you can find everyone whose surname starts with Lov; you cannot find everyone whose surname ends in lace.

"Index your foreign keys." A join matches rows in one table against another. Without an index on the referencing column, that means scanning the child table for every parent row. With one, each match is a seek. MySQL creates an index for a foreign key automatically for this reason - but a column that logically references another without a declared constraint gets nothing, and that is the one people miss.

"Composite indexes work left to right." The phone book, one more time. (surname, forename, city) is sorted by surname, then by forename within each surname, then by city within each pair. A filter on surname seeks. A filter on surname and forename seeks further. A filter on forename alone has no starting point in this ordering, because forenames are scattered across every surname.

Which means the column order in a composite index is a decision, not a formality. (surname, forename) and (forename, surname) are different indexes answering different questions, and having both is sometimes correct.

The part that is MySQL-specific

InnoDB adds a twist worth knowing, because it explains advice that otherwise sounds arbitrary.

In InnoDB the table is the primary key index. Rows are stored inside the leaves of that B-tree, in primary key order. The table has no separate existence - this is called a clustered index.

Three things follow.

A primary key lookup is one descent. You arrive at the leaf and the row is there. No second step.

Every secondary index stores the primary key as its pointer. It has to, because that is how rows are addressed. So looking a row up through a secondary index is two descents - one through the secondary index to find the primary key, another through the clustered index to find the row. And a long primary key inflates every other index on the table, because each entry carries a copy of it.

An increasing primary key matters. New rows with increasing keys append to the end of the tree. Random keys - a UUID - land in the middle, splitting pages and fragmenting the structure. This is why AUTO_INCREMENT beats a random UUID as a primary key, and why the usual advice is to keep the UUID in its own unique column if you need one.

The covering index

Put the two halves together and something falls out.

A secondary index lookup is normally two descents: find the primary key, then find the row. But if the index already contains every column the query needs, the second descent is unnecessary. The answer is in the sorted copy.

CREATE INDEX idx_orders_cover ON orders (customer_id, placed_at, total);

SELECT customer_id, placed_at, total
FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC;

Every column named - in the filter, the sort and the result - is in the index. The query is answered entirely from it. EXPLAIN reports Using index, and it is frequently the largest single improvement available on a query that is already using an index.

It also explains a rule that sounds like style advice: do not write SELECT * in application code. Beyond fetching columns you do not need, it makes a covering index impossible, because the index can never contain every column. Naming three columns instead of all of them can be the difference between one descent and two, on every row returned.

And because the copy is sorted, an index can satisfy an ORDER BY without sorting at all. A query filtering on customer_id and ordering by placed_at, served by an index on (customer_id, placed_at), reads the rows out in the order it wants them. Without it, EXPLAIN says Using filesort, and for a large result that sort happens on disk.

One query, before and after

Here is the whole argument on a single query. An orders table with 2.4 million rows, and a page showing one customer's twenty most recent orders.

SELECT reference, total
FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC
LIMIT 20;

EXPLAIN before any index:

type: ALL          -- full table scan
key:  NULL         -- no index used
rows: 2400000      -- examined
Extra: Using filesort

Two and a half million rows read, then every matching row sorted, to return twenty. Note that LIMIT 20 saves nothing here: the server cannot know which twenty are most recent until it has found and sorted them all.

Now ask the question from the article rather than reaching for a rule. What would this data have to be sorted by for the answer to be a seek? By customer_id first, so there is somewhere to seek to. Then, within each customer, by placed_at descending, so the twenty most recent are the first twenty entries at that position.

CREATE INDEX idx_orders_customer_placed ON orders (customer_id, placed_at);
type: ref
key:  idx_orders_customer_placed
rows: 31
Extra:                 -- no filesort

Thirty-one rows examined instead of 2.4 million, and the sort has vanished entirely — because the index is already in that order, so reading forwards from the seek position produces the rows in the order the query asked for.

Add total and reference to the index and Extra becomes Using index: the rows themselves are never fetched at all.

Nothing about the query changed. The data was simply sorted by something useful.

So why not index everything?

Because the copies are real. Each one occupies disk, must be kept in memory to be useful, and must be updated on every write that touches its columns.

On a read-heavy table, more indexes is usually right. On a write-heavy one, each index is a tax on every insert. And an index nobody uses is pure cost — which is the argument for looking at sys.schema_unused_indexes on any schema you inherit, where you will usually find several, along with duplicates: an index on (surname) sitting beside one on (surname, forename), when the second already serves every query the first could.

What to do instead of guessing

The reason to understand the mechanism rather than memorise the rules is that it changes what you do when a query is slow.

Guessing produces tables with fifteen indexes, none of which the slow query uses, and an insert rate that has quietly halved. The alternative is three steps.

Run EXPLAIN first. Before adding anything. Look at type — ALL is a full scan. Look at key — frequently NULL when you expected an index. Look at rows against the number actually returned; a query examining two million rows to return twenty is the gap you are closing.

Ask what order would answer this. Not "which column should I index" but "what would this data have to be sorted by for the answer to be a seek". The filter columns first, in the order they are tested for equality; then the range column; then the sort column. That ordering is the index.

Check Extra. Using filesort means the sort is not served by an index. Using temporary means a temp table was built. Using index means you got a covering index and should stop optimising.

One idea, not seven rules

Everything above came from one sentence. A sorted copy with a pointer back.

Leftmost prefixes, functions defeating indexes, prefix-only LIKE, covering indexes, SELECT * being a performance question, UUIDs as primary keys, free sorting, the write cost — all of it is that single structure seen from a different angle.

The useful consequence is that you can work out the ones nobody told you about. Faced with an unfamiliar query, the question is never "which rule applies here". It is: what would this data have to be sorted by, for the answer to be a seek instead of a scan?

Answer that, and you have designed the index.


Indexes, storage engines and reading an EXPLAIN plan are Module 9 of MySQL: From Query to Production, free to read in full.

mysql sql indexing performance databases query optimisation