MySQL Finding Index Information

Going to make this short and sweet.  I was recently dealing with an index and want to know the size of one specific index on a table.



For a non partitioned table:
select sum(stat_value) pages, index_name, sum(stat_value)*@@innodb_page_size size from mysql.innodb_index_stats where table_name='<name>' and stat_description like 'Number of pages%' group by index_name;
For a partitioned table:
select sum(stat_value) pages, index_name, sum(stat_value)*@@innodb_page_size size from mysql.innodb_index_stats where table_name like '<table_name>#P%' and stat_description like 'Number of pages%' group by index_name;

For a partitioned table you need to add #P% to the table name this will include all the indexes on partitioned.

1 comment: