Re: Estimating Index Size on Innodb Tables

[Rick James]

There are several things missing from that simplistic computation:

* BTrees have inherent overhead: Suggest multiply by 1.4.

* A VARCHAR needs the length: add 1 byte (maybe more)

* There is overhead on each row of the index. (It is something like 29 bytes for _data_ rows; I don't know if it is the same for _index_ rows.)

* OTOH, there may be "prefix" compression. This would shrink the size because all the rows in a "block" would have the first few characters identical.

* InnoDB implicitly has the PRIMARY KEY in every secondary key. (Otherwise, how would it find the row!) Your PK is a 4-byte INT, so that is another 4 (probably 5) bytes.

* The "plugin" possibly works differently.

Let's try
300024 * (6.22 + 1 + 5 + 29) * 1.4 = 17MB
300024 * (6.22 + 1 + 5) * 1.4 = 5MB
Well, neither of these is quite right.