Query MySQL database size meta

get-mysql-database-size-meta.sql

# Get the size meta of every accessible schema
SELECT
    count(*) tables,
    `table_schema`,
    format(sum(`table_rows`)/1000000, 3) `rows (M)`,
    format(sum(`data_length`)/(1024*1024*1024), 3) `data_size (GB)`,
    format(sum(`index_length`)/(1024*1024*1024), 3) `index_size (GB)`,
    format((sum(`data_length`+`index_length`))/(1024*1024*1024), 3) `total_size (GB)`,
    format(sum(`index_length`)/sum(`data_length`), 3) `index_data_ratio`
FROM
    `information_schema`.`TABLES`
GROUP BY
    `table_schema`
ORDER BY
    (sum(`data_length`+`index_length`))/(1024*1024*1024) DESC # Total Size
LIMIT 0,1000;


# Get the size meta of every table in a schema
SELECT
    `table_schema`,
    `table_name`,
    format(`table_rows`/1000, 2) `rows (K)`,
    format(`data_length`/(1024*1024), 2) `data_size (MB)`,
    format(`index_length`/(1024*1024), 2) `index_size (MB)`,
    format((`data_length`+`index_length`)/(1024*1024), 2) `total_size (MB)`,
    format(`index_length`/`data_length`, 2) `index_data_ratio (MB)`
FROM
    `information_schema`.`tables`
WHERE
    `table_schema` = 'SCHEMA_NAME_HERE'
ORDER BY
    (`data_length`+`index_length`)/(1024*1024) DESC # Total Size
LIMIT 0,1000;

Thanks to http://blog.kuroichiaki.com/2011/07/mysql-check-table-size-index-size-number-of-rows-and-total-size/ for the very strong starting point on these. 😃