MySQL: How to get the size of tables

You can get the size of tables by using information_schema in MySQL.

Query

List top 10 biggest size of tables.

SELECT table_schema,
       table_name,
       table_rows,
       avg_row_length,
       floor((data_length + index_length + data_free)/1024/1024) AS 'total(mb)',
       floor((data_length)/1024/1024) AS 'data(mb)',
       floor((index_length)/1024/1024) AS 'index(mb)',
       floor((data_free)/1024/1024) AS 'free(mb)'
FROM information_schema.tables
ORDER BY 5 DESC
limit 10;

column	description
table_name	table name
table_rows	table rows count
avg_row_length	average row data length
data_length	table data length
index_length	table index data length
all_data_length	table data + index data length

Reference

• Optimize disk storage when Amazon RDS for MySQL uses more storage than expected

Load more