When we are estimating the table size for mySQL table, why do we add primary key size twice? I saw this post How to estimate/predict data size and index size of a table in MySQL , and I noticed that the size of primary key is already in the row size. Why do we add the primary key size again?


why do we add primary key twice?

This is an effect of InnoDB index structure.

MySQL 8.0 Reference Manual / ... / Clustered and Secondary Indexes

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

In table structure shown in the thread there is 2 indices: primary (by ID) and secondary (by CountryCode). The PK expression value is included in both - explicitly in PK and implicitly in SI. It is described in RolandoMySQLDBA's answer.

If there were three indexes in the table, the size of the primary index would be counted three times. 4 indexes - 4 times. And so on...

If no PK / UK in the structure then InnoDB adds inner 6-byte record number as a PK, and you must add 6 * (1 + indices count) bytes to each record's indices storage size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.