How to figure out size of Indexes in MySQL

Here's an adaption from some of the above to also give you the percentage of the total index for the table that each index has used, hopefully this will be useful for someone

select 
    database_name, 
    table_name, 
    index_name, 
    round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB, 
    round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis 
where stat_name='size' 
and table_name = 'TargetTable'
and database_name = 'targetDB'

Example output

database_name   table_name  index_name  SizeMB  Percentage
targetDB        TargetTable id          10      55.55
targetDB        TargetTable idLookup    5       27.77
targetDB        TargetTable idTest      3       16.66

Regards Liam

On MyISAM, each index block is 4 KB page filled up to fill_factor with index records, each being key length + 4 bytes long.

Fill factor is normally 2/3

As for InnoDB, the table is always clustered on the PRIMARY KEY, there is no separate PRIMARY KEY index

Using phpMyAdmin, when viewing the table structure there is a Details link at the bottom somewhere. Once you click on it it will show you the total size of the indexes you have on the table where it is marked Space Usage.

I don't think it shows you each index individually though.

From the MySQL 5.6 reference

SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats WHERE table_name='t1'
AND stat_name = 'size' GROUP BY index_name;