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.


select table_schema as database_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,
order by used_mb desc;


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


  • 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.

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