List tables by their size in MariaDB database

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 hereand table_type = 'BASE TABLE'group by table_schema,
         table_name
order by used_mb desc;

Columns

1. database_name - table database (schema) name
2. table_name - table name
3. used_mb - space used in MB
4. allocated_mb - space allocated in MB

Rows

1. One row: represents one table in a database (schema)
2. Scope of rows: all tables in a database (schema)
3. 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.

1. Original blog post- https://dataedo.com/kb/query/mariadb/list-of-tables-by-their-size