View the size of mysql tables that are too heavy

Note: This article is a reminder to my future self, so that next time I don't have to Google it again.

In some cases it is interesting to know why your Drupal database weighs several GB, and knowing which are the tables that weigh more can tell us what may be the problem. It is not normal to have such large databases, and it will surely give us an idea of which custom module is messing it up.

With this sql command you get a list of the tables sorted in order from most to least weight and showing the weight in MB.

SELECT table_name AS "Tables", 
       round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "drupal"
ORDER BY (data_length + index_length) DESC;

You have to replace in table_schema "drupal" by the name of the database we want to query.

In my case, in my local environment my database is always called "drupal."

Have Any Project in Mind?

If you want to do something in Drupal maybe you can hire me.

Either for consulting, development or maintenance of Drupal websites.