Query below returns list of indexes in a database with space they use ordered from the ones using most.
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.
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;
- 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
- 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
Indexes in our test database from the ones using most space to least.