Database Table Size

[TumTum]

A command that calculates the sizes of the tables from the database and displays them on the console as a table.

This would be the SQL command:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES WHERE `TABLE_TYPE` = "BASE TABLE"
ORDER BY (data_length + index_length) DESC;

[keywan-ghadami-oxid]

it can be usefull to get also the size of index like they do in the maria db documentation:

SELECT  ENGINE,
        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
        COUNT(*) "Num Tables"
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")
    GROUP BY  ENGINE;```
see https://mariadb.com/kb/en/library/mariadb-memory-allocation/
the combination with your statement could be very useful for different usecases.

[TumTum]

is published