Query below returns tables in a database with space used by their indexes ordered from the ones using most.
select table_schema as database_name, table_name, round(index_length/1024/1024,2) as index_size from information_schema.tables where table_type = 'BASE TABLE' and table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql') -- and table_schema = 'your database name' order by index_size desc;
- database_name - database (schema) name
- table_name - table name
- index_size - disk space used by indexes
- One row represents one table
- Scope of rows: all tables in database with index space used
- Ordered by table indexes size descending