Query below returns list of indexes in a database with space they use ordered from the ones using most.

Notes

Query results are only for tables which uses InnoDB storage engine. Additionaly, if clustered index have specified constraint name it will appear in results too.

Query

select database_name,
       index_name,
       (1.0*stat_value*@@innodb_page_size/1024/1024) as index_size,
       table_name
from mysql.innodb_index_stats
where stat_name = 'size'
      and index_name not in ('PRIMARY', 'GEN_CLUST_INDEX')
      -- and database_name = 'put your database name here'
order by index_size desc;

Columns

• database_name - name of the database (schema)
• index_name - name of index
• index_size - space used in MB by index
• table_name - name of the table

Rows

• One row represents one index in a database
• Scope of rows: all indexes in a database
• Ordered by index used size, from largest to smallest

Sample results

Indexes in our test database from the ones using most space to least.