List tables by their size in MySQL database

The query below returns tables in a database (schema) along with the space they use, sorted from the largest use of space to the smallest.

Query

select table_schema as database_name,
    table_name,
    round(sum((data_length + index_length)) / power(1024, 2), 2) as used_mb,
    round(sum((data_length + index_length + data_free)) /
          power(1024, 2), 2) as allocated_mb
from information_schema.tables
where table_schema = 'your database name' -- put your database name here
    and table_type = 'BASE TABLE'
group by table_schema,
         table_name
order by used_mb desc;

Columns

  • database_name - table database (schema) name
  • table_name - table name
  • used_mb - space used in MB
  • allocated_mb - space allocated in MB

Rows

  • One row: represents one table in a database (schema)
  • Scope of rows: all tables in a database (schema)
  • Ordered by: size used by the table, from largest to smallest

Sample results

Tables in the Sakila database (schema), sorted from those with more space to the smallest ones.

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