List tables by their size in MariaDB 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.

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