Mysql list tables and sizes - order by size

Question

What would be the query to list all tables in a database order by their size in mysql?

[What have you tried?](http://www.whathaveyoutried.com/) See [ask advice](http://stackoverflow.com/questions/ask-advice), please. — John Conde, Jan 28 '13 at 19:49
possible duplicate of [How to get the sizes of the tables of a mysql database?](http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database) — Kermit, Jan 28 '13 at 19:49
Check http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/ — Ravindra Gullapalli, Jan 28 '13 at 19:51
Related, if it's of interest, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38693721). — Drew, Aug 01 '16 at 15:37

score 105 · Accepted Answer · edited Jun 26 '21 at 05:04

105

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

edited Jun 26 '21 at 05:04

Cave Johnson

6,499
5
38
57

answered Jan 28 '13 at 19:51

MG_Bautista

2,593
2
18
33

score 6 · Answer 2 · answered Jul 20 '15 at 06:28

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;

score -1 · Answer 3 · answered Jan 03 '19 at 08:36

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;

Mysql list tables and sizes - order by size

3 Answers3