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?

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.

If you are using phpmyadmin then just go to the table structure

e.g.

Space usage
Data 1.5 MiB
Index 0 B
Total 1.5 Mi

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;

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

Another way of showing the number of rows and space occupied and ordering by it.


SELECT
table_schema as `Database`,
table_name AS `Table`,
table_rows AS "Quant of Rows",
round(((data_length + index_length) / 1024 / 1024/ 1024), 2) `Size in GB`
FROM information_schema.TABLES
WHERE table_schema = 'yourDatabaseName'
ORDER BY (data_length + index_length) DESC;

The only string you have to substitute in this query is "yourDatabaseName".

Adapted from ChapMic's answer to suite my particular need.

Only specify your database name, then sort all the tables in descending order - from LARGEST to SMALLEST table inside selected database. Needs only 1 variable to be replaced = your database name.

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_HERE"
ORDER BY size DESC;

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.

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).

If you want a query to use currently selected database. simply copy paste this query. (No modification required)

SELECT table_name ,
round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;