Clustered Indexes, Secondary Indexes, Hidden Columns, and Record Layout

Learning outcomes

InnoDB does not store a heap of rows plus independent indexes. Every InnoDB table is organized around one clustered index whose leaf records contain the row data. That single fact explains why primary-key choice influences table locality, why secondary indexes include the clustered key, and why a “harmlessly wide” primary key can make every secondary index larger.

Create three clustered-index cases

USE servicehub_innodb_lab;DROP TABLE IF EXISTS pk_narrow_demo, pk_wide_demo, no_pk_demo;CREATE TABLE pk_narrow_demo (  row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  external_key CHAR(36) NOT NULL,  status VARCHAR(16) NOT NULL,  payload VARCHAR(200) NOT NULL,  PRIMARY KEY (row_id),  UNIQUE KEY uq_narrow_external (external_key),  KEY ix_narrow_status (status)) ENGINE=InnoDB;CREATE TABLE pk_wide_demo (  external_key CHAR(36) NOT NULL,  status VARCHAR(16) NOT NULL,  payload VARCHAR(200) NOT NULL,  PRIMARY KEY (external_key),  KEY ix_wide_status (status)) ENGINE=InnoDB;CREATE TABLE no_pk_demo (  external_key VARCHAR(64) NULL,  status VARCHAR(16) NOT NULL,  payload VARCHAR(200) NOT NULL,  KEY ix_no_pk_status (status)) ENGINE=InnoDB;

In pk_narrow_demo, the clustered key is an 8-byte integer. In pk_wide_demo, it is a 36-character external key. In no_pk_demo, no declared key qualifies, so InnoDB creates an internal clustered index named GEN_CLUST_INDEX. The internal identifier is an engine implementation mechanism, not an application-visible primary key you should depend on.

SHOW INDEX FROM pk_narrow_demo;SHOW INDEX FROM pk_wide_demo;SHOW INDEX FROM no_pk_demo;SELECT t.NAME AS table_name, i.NAME AS index_name, i.TYPE, i.N_FIELDSFROM information_schema.INNODB_TABLES AS tJOIN information_schema.INNODB_INDEXES AS i ON i.TABLE_ID=t.TABLE_IDWHERE t.NAME LIKE 'servicehub_innodb_lab/%_demo'ORDER BY t.NAME, i.TYPE DESC, i.NAME;

SHOW INDEX does not list the hidden row identifier as a normal SQL column. INNODB_INDEXES can expose GEN_CLUST_INDEX to a diagnostic account with PROCESS privilege.

How InnoDB chooses the clustered index

The clustered leaf record contains the row’s columns. A lookup by clustered key reaches the row in that B-tree. A secondary-index lookup first reaches a secondary leaf entry; if the query needs columns not covered there, InnoDB uses the embedded clustered key to perform the clustered-index lookup.

Secondary indexes carry the clustered key

Populate the narrow and wide tables with the same logical rows. We use a recursive CTE only to create a moderate lab dataset; the point is the resulting index structure, not the CTE itself.

INSERT INTO pk_narrow_demo(external_key,status,payload)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 1000)SELECT CONCAT('00000000-0000-0000-0000-',LPAD(n,12,'0')),       IF(n%3=0,'open','closed'), REPEAT('x',80)FROM seq;INSERT INTO pk_wide_demo(external_key,status,payload)SELECT external_key,status,payload FROM pk_narrow_demo;ANALYZE TABLE pk_narrow_demo, pk_wide_demo;

Then inspect page-count estimates:

SELECT NAME, NUM_ROWS, CLUST_INDEX_SIZE, OTHER_INDEX_SIZEFROM information_schema.INNODB_TABLESTATSWHERE NAME IN ('servicehub_innodb_lab/pk_narrow_demo',               'servicehub_innodb_lab/pk_wide_demo')ORDER BY NAME;

Do not expect one universal ratio from 1,000 tiny rows. The useful observation is directional: the wide clustered key has to appear in its secondary index entries, so as the table grows it can consume more index space and more cache bandwidth. Measure on a representative dataset before assigning a business impact.

Observe a secondary-to-clustered lookup

EXPLAIN ANALYZESELECT statusFROM pk_narrow_demoWHERE status='open'LIMIT 20;EXPLAIN ANALYZESELECT status,payloadFROM pk_narrow_demoWHERE status='open'LIMIT 20;

The first query can be satisfied from the secondary index if the selected data is covered there. The second needs payload, which is not in ix_narrow_status; InnoDB can use the clustered key stored in each matching secondary entry to reach the full clustered row. EXPLAIN ANALYZE gives actual iterator timing and row counts for that execution, but small warm-cache tests should not be generalized into production latency claims.

Tempting but ineffective tuning: add another status-only index

Suppose the noncovering query is slower on a larger workload. Adding another index on exactly the same status column does not solve the extra clustered lookup:

CREATE INDEX ix_narrow_status_duplicate ON pk_narrow_demo(status);EXPLAIN ANALYZESELECT status,payloadFROM pk_narrow_demoWHERE status='open'LIMIT 20;SHOW INDEX FROM pk_narrow_demo;DROP INDEX ix_narrow_status_duplicate ON pk_narrow_demo;

The corrected action depends on workload. A composite covering index such as (status, payload) could avoid some row lookups but would make writes and storage more expensive, and indexing a 200-character payload may be a poor tradeoff. The right lesson is not “cover everything”; it is to identify whether the cost is predicate filtering, clustered lookups, sort work, or data volume before adding an index.

Primary-key locality and random identifiers

A monotonically increasing numeric key tends to insert near one end of the clustered B-tree. A random UUID-like key distributes inserts across the key space. That can change page-split behavior, locality, and cache/I/O patterns. It does not mean random identifiers are forbidden: distributed identity, offline generation, or data-merging requirements can justify them. Chapter 08 will compare index-engineering choices in more depth.

Hands-on verification

SHOW CREATE TABLE pk_narrow_demo\GSHOW CREATE TABLE pk_wide_demo\GSHOW CREATE TABLE no_pk_demo\GSELECT t.NAME AS table_name, i.NAME AS index_name, i.TYPE, i.N_FIELDSFROM information_schema.INNODB_TABLES AS tJOIN information_schema.INNODB_INDEXES AS i ON i.TABLE_ID=t.TABLE_IDWHERE t.NAME IN ('servicehub_innodb_lab/pk_narrow_demo',                 'servicehub_innodb_lab/pk_wide_demo',                 'servicehub_innodb_lab/no_pk_demo')ORDER BY table_name,index_name;

One logical query can touch two B-trees

The “secondary index contains the primary key” rule becomes easier to remember if you follow one lookup. Suppose the application asks for all open rows and needs only status plus the primary key. The ix_narrow_status secondary B-tree already contains status and the clustered key, so the query can often stay in that index. If the application also requests payload, the secondary leaf entry does not contain that column; InnoDB uses the clustered key from the leaf entry to find the full row in the clustered B-tree.

This is why “number of indexes” is a poor way to reason about access cost. A query can use one secondary index yet still perform many clustered lookups. Conversely, a carefully designed covering index can eliminate those lookups for one workload but consume more storage, more buffer-pool pages, and more DML maintenance. Chapter 08 will make that tradeoff explicit with composite and covering index design.

The UNIQUE NOT NULL fallback is real, but still not a design target

InnoDB’s second-choice clustered-key rule is useful for understanding inherited schemas. It should not become a trick where you intentionally omit PRIMARY KEY and hope the “right” unique index is chosen. A primary key communicates row identity to humans, tools, ORMs, replication/operations workflows, and schema reviewers. Be explicit even when an equivalent unique key could technically become clustered.

Record layout is not an API

InnoDB records also contain internal fields that support transactions and row-version navigation. Their exact byte layout is engine implementation detail and can vary by row format and release. For application architecture, the durable insight is simpler: the clustered record carries the row, secondary records carry their secondary key plus clustered key, and MVCC needs internal metadata/undo links. Avoid application logic that depends on undocumented page offsets or record-header bytes.

Production judgment and bridge

Choose a primary key for correctness first: stable identity, uniqueness, non-nullability, and application semantics. Then consider its physical cost because InnoDB clusters the table by that key and repeats it through secondary indexes. Do not redesign a proven schema solely because a synthetic benchmark shows a tiny difference; measure index size, buffer behavior, write rates, and actual plans.

Lesson 3 moves from disk organization to memory behavior: which pages are cached, what “dirty” means, how the buffer pool ages pages, and why 8.4’s change-buffer and adaptive-hash defaults must be checked rather than assumed.

Authoritative references

• MySQL 8.4 Reference Manual — Clustered and Secondary Indexes
• MySQL 8.4 Reference Manual — INNODB_INDEXES
• MySQL 8.4 Reference Manual — INNODB_TABLESTATS
• MySQL 8.4 Reference Manual — EXPLAIN ANALYZE