Find out MySQL index size for a concrete index

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.

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

StackExchange Database Administrators Q#12354, answer score: 6