How to figure out size of Indexes in MySQL

Extending Vajk Hermecz’s answer.
This is how you can get all of the indexes size, in megabytes, without the PRIMARY (which is the table itself), ordered by size.

SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name="size" AND index_name != 'PRIMARY'
ORDER BY size_in_mb DESC;

Leave a Comment