In MySQL's default storage engine, InnoDB, a table isn't a pile of rows with indexes on the side. The table is the primary key index. Once that clicks, a lot of InnoDB performance advice suddenly makes sense.
The clustered index
InnoDB stores every table as a B+tree ordered by the primary key. The leaf pages of that tree hold the complete rows. This is the clustered index.
If you don't define a primary key, InnoDB picks one for you:
- the first
UNIQUEindex whose columns are allNOT NULL, or otherwise - a hidden 6-byte row ID (
GEN_CLUST_INDEX) that you can't use in queries.
Always define an explicit primary key.
Secondary indexes store the primary key
A secondary index's leaf entries contain the indexed columns plus the primary key value — not a physical pointer. To fetch the rest of a row, InnoDB takes that PK value and searches the clustered index a second time.
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_customer (customer_id)
) ENGINE=InnoDB;
Here, every entry in idx_customer is effectively (customer_id, id).
Consequence 1: wide primary keys bloat every index
Because the PK is copied into every secondary index, a 36-character CHAR(36) UUID primary key makes all your indexes much bigger than an 8-byte BIGINT would. Bigger indexes mean fewer entries per page, more memory pressure on the buffer pool and more I/O.
