Snippet: MySQL table sizes ordered by largest to smallest (in MB and Row Count)
Show All Tables By Size in MB
|
1 |
SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC; |
Show All Tables By Size in MB (if > 1 MB)
|
1 |
SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() AND (data_length + index_length) > 1048576 ORDER BY (data_length + index_length) DESC; |
Show All Tables By Number of Rows
|
1 |
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() ORDER BY table_rows DESC; |