Friday, 19 October 2012

Check the table size of a MySQL DB

I wanted to create a MySQL dump of a small DB, so run mysqldump and realized that what I was expecting to take a few MB was instead half a GB.
Where did things go wrong? Which table is storing such an unexpected amount of data?

Googling around I've found a few interesting posts on how to check the size of a table, and then selected this one.

My suggested approach is:
SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name";
where schema_name is the DB you're interested in.

I've then immediately spotted the "offending" table :-)

1 comment:

  1. I like little queries like this...the information_schema is a much under utilised resource by most people. You can also find this out by just listing the contents of the data directory on the server...but you'll have to have file-per-table switched on for innodb to do that ;-)