What would be the query to list all tables in a database order by their size in mysql?
-
1What have you tried? See ask advice, please.John Conde– John Conde2013-01-28 19:49:06 +00:00Commented Jan 28, 2013 at 19:49
-
possible duplicate of How to get the sizes of the tables of a mysql database?Kermit– Kermit2013-01-28 19:49:21 +00:00Commented Jan 28, 2013 at 19:49
-
Check mysqlperformanceblog.com/2008/02/04/…Ravindra Gullapalli– Ravindra Gullapalli2013-01-28 19:51:17 +00:00Commented Jan 28, 2013 at 19:51
-
Related, if it's of interest, I wrote a Describe All Tables in this Answer.Drew– Drew2016-08-01 15:37:46 +00:00Commented Aug 1, 2016 at 15:37
Add a comment
|
3 Answers
Try this...
SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name"
ORDER BY (data_length + index_length) DESC;
Note: Replace schema_name above with the name of your database
Comments
Run the following query in the mysql client
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;
Comments
Execute following query in information_schema database:
SELECT table_schema AS "Database name",
SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY (SUM(data_length + index_length) / 1024 / 1024) DESC;
