Rows live in the index
In InnoDB the table itself is a clustered index: the actual row data is stored in the leaf pages of a B plus tree ordered by the primary key. There is no separate heap. Looking up a row by primary key walks the tree directly to the data.
Choosing the primary key
Because data is physically ordered by the primary key, the choice matters:
- An auto increment integer key appends rows in order, keeping inserts cheap and pages tightly packed.
- A random key like a UUID scatters inserts across the tree, causing page splits and fragmentation.
When you do not pick one
If you do not define a primary key, InnoDB uses the first non null unique index, and if none exists it generates a hidden 6 byte row id. Relying on the hidden key is wasteful, so always define a deliberate primary key.
Why it matters
Every secondary index stores the primary key as its pointer to the row. A wide primary key therefore bloats every secondary index, so a narrow key saves space across the whole table.
Key idea
InnoDB stores each table as a clustered index ordered by the primary key, so primary key lookups reach data directly and the key choice shapes insert cost and index size.