--- title: "Why the Primary Key Is in Every Secondary Index" description: "InnoDB stores the table inside its primary key. Almost every MySQL indexing consequence, good and bad, follows from that single decision." url: https://sqlpress.com/blog/innodb-clustered-index/ category: MySQL tags: ["indexing", "schema-design", "query-optimization", "innodb"] author: "Elias Rowe" published: 2026-09-20T00:00:00.000Z measured: false --- # Why the Primary Key Is in Every Secondary Index ## Key takeaways - In InnoDB the table is the primary key index — row data lives in the leaf pages of that B-tree, not in a separate heap. - Every secondary index stores the primary key as its pointer, so a wide primary key makes every other index wider. - A secondary index lookup that needs columns it does not contain performs a second traversal through the primary key, not a direct row read. - The argument does not transfer to PostgreSQL, which keeps the table in a heap and puts no implicit primary key inside a secondary index. ## Test environment - System: MySQL 8.4 - System: PostgreSQL 18 - System: SQL Server 2022 - Workload: Illustrative (examples verified, nothing timed) - Notes: MySQL is the subject: the InnoDB storage engine, where the table itself is the primary key B-tree. PostgreSQL's heap and the SQL Server Key Lookup appear only as comparisons. InnoDB does not store rows in a heap with indexes pointing into it. The table *is* an index: a B-tree keyed on the primary key, with the full row stored in the leaf. This is what "clustered index" means, and much of MySQL's indexing advice is a downstream consequence of it. ## What does a MySQL secondary index actually store? Since there is no fixed row location to point at — rows move as pages split — a secondary index cannot store a physical address. It stores the primary key value instead. So an index on `(email)` in a table with primary key `id` physically contains `(email, id)`. You did not declare `id`; it is there regardless. Two consequences follow immediately. **Every secondary index inherits the primary key's width.** A `BIGINT` primary key adds 8 bytes to every entry of every secondary index. A 36-character UUID stored as `CHAR(36)` adds 36 — the value is ASCII, so it costs one byte per character under the compact row formats InnoDB has defaulted to since 5.7; the general formula is `M` times the width of the widest character in the charset. On a table with five indexes and a hundred million rows, the 28-byte difference is fourteen gigabytes, and those bytes displace other pages in the buffer pool. **Some queries are covered by accident.** `SELECT id FROM users WHERE email = ?` never touches the clustered index, because `id` is already in the secondary index. This is why MySQL developers often get covering-index behavior without having asked for it, and why the same query rewritten to select one more column can turn into a second traversal. ## The second traversal When a secondary index does not contain everything the query needs, the engine takes the primary key from the index entry and performs a second B-tree descent through the clustered index to find the row. ```sql title="two-traversals.sql" -- One traversal: email index contains email and id. SELECT id FROM users WHERE email = 'a@example.com'; -- Two traversals: email index, then clustered index for last_login_at. SELECT id, last_login_at FROM users WHERE email = 'a@example.com'; ``` For a single row this is a few extra page reads. For a range scan returning thousands of rows it is thousands of descents, each potentially a random read, and it is the point at which the optimizer abandons the index and scans the table instead — the same crossover, [argued from the row estimate for PostgreSQL](/blog/index-not-used-estimate-not-index/). SQL Server names the same second read in the plan as a Key Lookup operator; InnoDB performs it without naming it. ## What makes a good InnoDB primary key? Keep it narrow, because it is duplicated into every secondary index. Keep it monotonically increasing where you can. Inserts in ascending order leave index pages about 15/16 full; random order leaves them anywhere from 1/2 to 15/16 full, because the insert lands in an arbitrary page and splits it. A randomly distributed UUID as a primary key is the combination of both problems: wide and unordered. If a random identifier is required externally, one common resolution is an auto-increment primary key for storage with a unique secondary index on the UUID for lookups. That keeps the clustered index narrow and ordered while preserving the external contract, at the cost of one extra index. ## Where PostgreSQL differs PostgreSQL uses a heap with all indexes pointing into it, including the primary key index. There is no clustered index and no implicit primary key inside secondary indexes. So the "narrow primary key" argument is much weaker there, and advice about UUID keys that is well justified for InnoDB is often repeated for PostgreSQL where the mechanism does not exist. This is a good example of why the mechanism matters more than the rule. The rule does not transfer; the reasoning shows you why. ## FAQ ### Why does every MySQL secondary index contain the primary key? InnoDB stores the table inside the primary key B-tree, and rows move between pages as those pages split, so there is no fixed physical address for a secondary index to point at. The secondary index stores the primary key value instead. An index declared on email in a table keyed on id physically contains email and id, whether or not that was asked for. ### Is a UUID primary key a problem in InnoDB? It combines two costs. A 36-character UUID stored as CHAR(36) adds 36 bytes of ASCII to every entry of every secondary index where a BIGINT would add 8, and those bytes displace other pages in the buffer pool. Randomly distributed values also insert into arbitrary pages rather than appending to the rightmost one, which causes page splits and leaves pages partly empty. ### Why does adding one column to a MySQL query make it much slower? A secondary index answers a query on its own only when it holds every column requested, and it always holds the primary key. Selecting a column the index does not contain forces a second B-tree descent through the clustered index, once per matching row. For a range scan returning thousands of rows that is thousands of potentially random reads. ### How do I keep a UUID identifier without a wide InnoDB primary key? One common resolution is an auto-increment primary key for storage with a unique secondary index on the UUID for lookups. The clustered index stays narrow and ordered, so every other index stays smaller and inserts still append to the rightmost page, while the external identifier contract is preserved. The cost is one extra index. ## Sources - [MySQL 8.4 Reference Manual — Clustered and Secondary Indexes](https://dev.mysql.com/doc/refman/8.4/en/innodb-index-types.html) (docs) - [MySQL 8.4 Reference Manual — InnoDB Index Structure](https://dev.mysql.com/doc/refman/8.4/en/innodb-physical-structure.html) (docs) - [MariaDB Documentation — InnoDB Row Formats Overview](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-row-formats/innodb-row-formats-overview) (docs) — The compact row formats are what make a CHAR(36) of ASCII cost 36 bytes rather than M times the charset width. - [MySQL 8.4 Reference Manual — Data Type Storage Requirements](https://dev.mysql.com/doc/refman/8.4/en/storage-requirements.html) (docs) - [PostgreSQL 18 documentation — Index-Only Scans and Covering Indexes](https://www.postgresql.org/docs/18/indexes-index-only-scans.html) (docs) — All PostgreSQL indexes are secondary; the table itself lives in a heap. - [Microsoft Learn — Showplan Logical and Physical Operators Reference](https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference) (docs) — Key Lookup is the SQL Server operator for the second read into the clustered index.