List tables by the size of their indexes in MariaDB database

Query below returns tables in a database with space used by their indexes ordered from the ones using most.

Query

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;

Columns

  • database_name - database (schema) name
  • table_name - table name
  • index_size - disk space used by indexes

Rows

  • One row represents one table
  • Scope of rows: all tables in database with index space used
  • Ordered by table indexes size descending

Sample results

0
There are no comments. Click here to write the first comment.