Posted By: Anonymous
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?
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;