What is a clustered index in InnoDB, and why does primary key choice matter so much?

Summary

InnoDB stores table rows inside the primary key B-tree (the clustered index), ordered by primary key. Every secondary index stores the primary key value as its row pointer, so lookups through a secondary index do a second search in the primary key.

Longer Answer

Consequences:

• Short primary keys keep every secondary index small, because each secondary entry contains the PK.
• Monotonic keys (AUTO_INCREMENT) append to the end of the B-tree. Random keys (UUIDv4) insert all over the tree, causing page splits, fragmentation and many more random I/Os once the table outgrows the buffer pool.
• Range scans on the primary key are fast because rows are physically ordered.
• If you define no primary key, InnoDB uses the first NOT NULL unique index, or creates a hidden 6-byte row ID, which you cannot use in queries and which is a shared counter.

If you need globally unique IDs, use time-ordered ones (UUIDv7, ULID, Snowflake-style) stored as BINARY(16), or keep an AUTO_INCREMENT PK and a separate unique public ID.

Key Points

• Rows live in the PK B-tree.
• Secondary indexes store the PK.
• Prefer short, increasing PKs.
• Random UUIDs cause page splits.

What interviewers look for

Understanding the secondary-index-to-PK lookup and the cost of random keys. This explains many real performance problems.

Follow-up questions

• Why can SELECT id FROM t WHERE email = ? be answered from the email index alone?