Mysql tables – calculating size on disk.

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.

Advertisements

2 thoughts on “Mysql tables – calculating size on disk.”

  1. 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s