InnoDB Clustered Primary Key and What a Secondary Index Lookup Costs in MySQL
MySQL's InnoDB stores the table itself inside the primary key B-tree, so rows live in primary key order and there is no separate heap. Every secondary index leaf therefore stores the primary key value instead of a physical address, and a lookup that needs extra columns walks the secondary index and then walks the primary key tree a second time. Practical consequences follow directly: a long or random primary key bloats every index, range scans on the primary key are unusually cheap, and MySQL plan advice does not always transfer to PostgreSQL.
Questions this Concept answers
- Why does a long, randomly generated primary key hurt an InnoDB table more than it would hurt a PostgreSQL table?
Clustered and Nonclustered Indexes in SQL Server and When Each One Avoids a Table Scan
A database index is an auxiliary ordered structure that lets the query engine locate qualifying rows directly instead of performing a full table scan, and relational engines distinguish two kinds: a …