List tables by the size of their indexes in MySQL database

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,
       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

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.