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.
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.