How to get the sizes of the tables of a mysql database?

I can run this query to get the sizes of all tables in a mysql database:

show table status from myDatabaseName;

I would like some help in understanding the results. I am looking for tables with the largest sizes.

Which column should I look at?

 Answers

You can use this query to show the size of a table (although you need to substitute the variables first):

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

or this query to list the size of every table in every database, largest first:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

---

SELECT 
    table_name AS "Table",  
    round(((data_length + index_length) / 1024 / 1024), 2) as size   
FROM information_schema.TABLES  
WHERE table_schema = "YOUR_DATABASE_NAME"  
ORDER BY size DESC; 

This sorts the sizes (DB Size in MB).

---

There is an easy way to get many informations using Workbench:

• Right-click the schema name and click "Schema inspector".
• In the resulting window you have a number of tabs. The first tab "Info" shows a rough estimate of the database size in MB.
• The second tab, "Tables", shows Data length and other details for each table.

---

Try the following shell command (replace DB_NAME with your database name):

mysql -uroot <<<"SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"DB_NAME\" ORDER BY (data_length + index_length) DESC;" | head

For Drupal/drush solution, check the following example script which will display the biggest tables in use:

#!/bin/sh
DB_NAME=$(drush status --fields=db-name --field-labels=0 | tr -d '\r\n ')
drush sqlq "SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"${DB_NAME}\" ORDER BY (data_length + index_length) DESC;" | head -n20

---

Heres another way of working this out from using the bash command line.

for i in mysql -NB -e 'show databases'; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done

---

If you have ssh access, you might want to simply try du -hc /var/lib/mysql (or different datadir, as set in your my.cnf) as well.