Check Database and Table Space in MySQL

On this page:

This topic applies to MySQL Version 5 and newer.

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 that have Free Space

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.

For example: