Clustered indexes
The clustered index is not an index over the rows — it is where the rows are kept. Choosing a primary key therefore decides physical layout, and because every secondary index stores that key, the choice is paid for again in each one.
Think of a library where the catalogue card is the book. There is no shelf to walk to afterwards, because finding the card means you are already holding what you wanted — and every other index in the building has to quote the card's number to point at it.
A primary key is usually chosen as though the only question were uniqueness. On an engine that clusters, it is also a decision about where every row physically sits and about how large every other index on the table will be — which makes it one of the few schema choices that is expensive to revisit and easy to make casually.
The primary key is where the row lives#
MySQL’s documentation states the arrangement without hedging: each InnoDB table has a special index called the clustered index that stores row data, and typically the clustered index is synonymous with the primary key. The word doing the work is stores. This is not a structure that points at rows living somewhere else — the rows are in it, so a search leads directly to the page that contains the row data rather than to a pointer that then has to be followed.
Because the table has to be clustered on something, the engine will choose if you do not. The documented order is your PRIMARY KEY, failing that the first UNIQUE index with all key columns defined as NOT NULL, and failing both a hidden index named GEN_CLUST_INDEX over a synthetic row ID. That last case is worth noticing: a table with no declared key still has a clustered index, built on a column you cannot query, cannot see in the schema, and cannot use.
Every other index carries a copy of it#
If rows live inside the clustered index, then a secondary index cannot hold a physical address for a row, because rows shift as the clustered index reorganises. What it holds instead is the primary key. MySQL says so plainly: each record in a secondary index contains the primary key columns for the row as well as the columns specified for that index, and InnoDB uses the primary key value to search the clustered index for the row.
That single design decision produces the effect people notice as mysterious. The primary key is duplicated into every secondary index, once per row, so its width is multiplied by the number of secondary indexes and again by the row count. The documentation states the outcome flatly — if the primary key is long, the secondary indexes use more space. A wide natural key chosen for readability is paid for across the whole schema, in memory as well as on disk, because index pages compete for the same buffer pool as everything else.
It also explains the two-step nature of a non-primary lookup: find the entry in the secondary index, read the primary key out of it, then descend the clustered index to reach the row. That second descent is why a query the planner could answer from the secondary index alone is so much cheaper than one needing a column the index does not carry — which is one of the shapes behind a planner deciding your index is not worth using.
Where it goes wrong#
The classic mistake is a random primary key, and clustering is what turns it from a preference into a cost. Rows are stored in key order, so an increasing key appends each new row beside the last one and fills pages neatly. A random key scatters inserts across the whole structure, so every insert lands on a different page — pages that must be fetched, modified and eventually written, and that split when they fill. The write amplification is not exotic; it is the ordinary business of putting a row on a page, performed in the least convenient order possible.
The second is assuming this is how databases work rather than how one engine works. PostgreSQL keeps rows in a heap with indexes pointing into it, so its primary key is a constraint and an index but not a statement about physical placement. Identical schemas therefore behave differently on the two engines, and advice about key choice transfers badly between them — a caution that applies to almost everything about how an engine chooses to organise data on disk.
IF YOU REMEMBER ONE THING
On a clustering engine the primary key answers two questions at once: which rows are distinct, and where each one is kept. The second is the one that gets decided by accident.
Questions people also ask
4 QUESTIONSWhat happens if I do not define a primary key?
InnoDB picks one anyway. Its documentation gives the order: your PRIMARY KEY if there is one, otherwise the first UNIQUE index whose columns are all NOT NULL, and failing both, a hidden clustered index named GEN_CLUST_INDEX on a synthetic row ID column. So the table is always clustered on something — the only question is whether you chose it or the engine did.
Why does a long primary key make everything bigger?
Because each record in a secondary index contains the primary key columns as well as its own. That is how a secondary lookup finds the row: it reads the primary key value, then searches the clustered index with it. The documentation draws the consequence directly — if the primary key is long, the secondary indexes use more space, and they do so once per index per row.
Why is a lookup by primary key faster than by any other column?
Because there is no second step. The rows are stored in the clustered index itself, so the search leads directly to the page holding the row data. Any other index gets you a primary key value and then has to go and find the row with it, which is a second descent through a second structure.
Does every database work this way?
No, and assuming so is how the confusion starts. InnoDB always clusters; PostgreSQL stores rows in a heap and its indexes point into it, so a primary key there is a constraint plus an index rather than a decision about physical placement. The same schema therefore has different performance characteristics on the two engines for reasons that never appear in the schema.