MySQL queries for displaying allocation/usage of disk space for a database and its tables.

mysql-disk-size-queries.sql

/* View all tables with disk allocation/sizes shown */

SELECT 
    table_name,
    data_length/1048576,index_length/1048576, 
    (data_length+index_length)/1048576 as data_used,
    data_free/1048576 as data_free,
    (data_length+index_length+data_free)/1048576 as disk_used
FROM information_schema.tables where table_schema = 'main'
ORDER BY (data_length+index_length+data_free) ASC;



/* View all tables combined */

SELECT
   SUM(index_length)/1048576 as index_length,
   SUM(data_length)/1048576 as data_length,
   SUM(data_free)/1048576 as data_free,
   SUM(data_length+index_length+data_free)/1048576 as disk_used,
   (COUNT(*) * 300 * 1024)/1048576 as overhead,
   (SUM(data_length+index_length+data_free) + (COUNT(*) * 300 * 1024))/1048576+150 as estimated_actual_disk_usage
FROM information_schema.tables where table_schema = 'main';