Archive for December, 2011

List MySQL Table Space Consumption

Thursday, December 15th, 2011

Have you ever needed to print out a list of each table within MySQL and how much space was consumed?

Try this to list the top 20 space offenders:

SELECT engine, concat( table_schema, '.', table_name ) table_name,
concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
FROM information_schema.TABLES
ORDER BY ( data_length + index_length ) DESC LIMIT 20

Taken from a comment on the mysql developer docs site: http://dev.mysql.com/doc/refman/5.1/en/tables-table.html