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

itemprop="text">

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?

Answer

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;