Re: how index grows in size

[Rick James]

INT is 4 bytes.
You are asking about the size of the structure for
INDEX(int_field)
?

It will be a BTree (with a few exceptions). A BTree has some overhead due to "blocks" -- up to 100% overhead. For random inserts, the BTree will reach a steady state with about 40-50% overhead.

MyISAM index: Together with the INT is a 'pointer' to the data. The size of that pointer is controlled by myisam_data_pointer_size, which defaults to 6. The 6 limits the table size to 256^6 bytes for Dynamic tables. You can save a little bit of space by shrinking the 6. Bottom line: Plan on the INDEX taking about 15 ((4+6)*1.5) bytes times the number of rows. Actually, that only covers the "leaf" nodes of the BTree; however, that is about 99% of the bulk, so the computation is "good enough". 1T rows --> 15TB.

InnoDB PRIMARY KEY: The PK is embedded with the data, so it consumes virtually no overhead -- only the non-leaf nodes are extra. 1T rows -> a few GB, plus overhead on secondary keys.

InnoDB Secondary key: Each secondary key contains a copy of the fields that comprise the PK. So, the computation gets messy. Also, InnoDB has a hard-to-predict amount of overhead for each data or index rows. In older versions, the overhead was about 29 bytes/row; I think newer versions cost less. 1T rows -> ??

InnoDB without a PK: If you do not have an explicit PK, or a usable UNIQUE index, then InnoDB will provide a 6-byte number to use as the PK. This is added to your existing data. The 6B is, as above, added to each secondary index. It is almost always better to have an explicit PK on InnoDB tables.