Identify Largest Tables in MySQL Databases

Last week, as we were migrating to production the new version of our corporate web site, we came to realize that the database used on our dev / QA site had grown more than 20 times the size of the production DB.

Identifying the culprit table was however not that complicated. A simple SQL command allows to get the size of all tables in the DB:

1
SELECT s.schema_name,t.table_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") data_size,CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") index_size, t.ENGINE ENGINE, t.table_rows TABLE_ROWS,t.row_format TABLE_ROW_FORMAT,date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in ("mysql","information_schema") GROUP BY s.schema_name,t.table_name,TABLE_ROW_FORMAT,ENGINE ORDER BY TABLE_ROWS DESC,data_size DESC,index_size DESC;

In our case, the problem was coming from Drupal cache tables. Even though the tables were not copied across to production blindly, we decided that it was a great occasion to clear the cache. In order to do that, one can go one of two ways:

1. Empty the cache through phpMyAdmin for instance
2. Empty the cache from Drupal’s interface following this guide

The first method is probably the fastest way to go if you have admin access to the hosting infrastructure. On the other side, the second method can be deployed so that any admin on the website can clean the cache.