Today I was getting ready to move an application at work over to a staging app that we set up at Heroku. Not wanting to absorb any extra charges for loading up a copy of the entire production database I figured I would empty out the tables that where taking up the most disk space. For that I had to query the information_schema table and add the size of the table to the size of the indexes. All the values are in bytes you have to do a little multiplication to get it displaying in something meaningful to a human like megabytes. Since this is a neat trick that I will likely end up using again I thought I would put it up here as well. Here is the query:
mysql> select table_name, round(((data_length + index_length) / (1024*1024)),2) as “size in megs” from information_schema.tables where table_schema = “myapp_development”;
+———————-+————–+
| table_name | size in megs |
+———————-+————–+
| messages | 0.11 |
| organisations | 0.02 |
| rewards | 0.02 |
| schema_migrations | 0.02 |
| selections | 0.02 |
| senders | 0.02 |
| sessions | 0.64 |
| summaries | 15.52 |
| taggings | 0.05 |
| tags | 0.02 |
| transactions | 0.03 |
+———————-+————–+
11 rows in set (0.13 sec)
Change the where clause to look for the database you want or remove it completely to see all of them.
Excellent!
Here’s another variation I found:
SELECT `DATA_LENGTH`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = ‘my_database’
AND
`TABLE_NAME` = ‘my_table’
LIMIT 1;
Worked great for me, thank you.