sabato, gennaio 23, 2016

How to get the sizes of the tables of a mysql database

If you have a very big database with a lot of tables and you want to know which tables use more spaces, run the following query replacing %DB_NAME% with your database and %TABLE_NAME% if you know the table name.

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`,
    round((data_length / 1024 /1024), 2 ) `Data Length in MB` , 
    round((index_length / 1024 /1024), 2) `Index Length in MB` , 
    round(data_free / 1024 /1024, 2) `Free space in MB` 
FROM information_schema.TABLES 
where round(data_free/1024/1024) > 500 
    AND table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";


Ref: http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database