Find out MySQL index size for a concrete index

Question

In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ?

I know I can select the index_length from information_schema.tables, but this gives the total size of all indexes in the table.

Let's suppose I have a table with PK index, several FK indexes and unique constraints, etc. Can I find out the size of one of those indexes only? Let's say, I'd like to know the size of the PK index only.

Answer 1

There appears to be no way to get the answer out of mysql directly; however you can do a pretty good job of estimating it:

For innodb, PRIMARY index is part of the data itself and shows up in data_length. If a table has no other indexes index_length is 0.

For every row in your table, every non-PRIMARY (UNIQUE or INDEX) index stores

• 6 bytes overhead
• every column indexed
• every column in PRIMARY not already in the index
• 1 bit per possible NULL column, rounded up to the byte

The size of a column in an index is the space the actual data takes up plus 1 or 2 bytes of length per VARCHAR/TEXT/BLOB. NULLs take no space.

There is an additional overhead of about 1.4x to 2.8x for the B-tree pages and non-full leaf pages. In my databases it looks to be around 2x usually.

FKs should not have any space overhead beyond the index they require.

mysql docs - source