How to Check Database and Table Space in MySQL (Version 5+)
If you've ever wondered how much space your MySQL databases are consuming, or even the tables within those databases, then you may like one of these small scripts issued against the INFORMATION_SCHEMA tables.
Database Space Usage Report
SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC
Table Space Usage Report
SELECT s.schema_name, table_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
ORDER BY total_size DESC
Check for Tables with Free Space within them
SELECT s.schema_name, table_name,
IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
HAVING data_free > 100
ORDER BY data_free DESC
* This SQL will check for all objects with free space. It will display tables with any more than 100Kb of free space so you may want to tweak the having clause, but the idea is that it can spot tables which may benefit from an Optimize Table command.
e.g.
REQUEST A DEMO
START A FREE TRIAL