Top 20 MySQL tables sorted by size

mysql_tables_size.sql

-- MySQL: Check table size, index size, number of rows and total size
-- (c)http://blog.kuroichiaki.com/2011/07/mysql-check-table-size-index-size-number-of-rows-and-total-size/
-- (c)https://www.percona.com/blog/2008/03/17/researching-your-mysql-table-sizes/

-- Top 20 tables

SELECT CONCAT(table_schema,'.',table_name) tables,
CONCAT(ROUND(table_rows/1000000,2),'M') rows_cnt,
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') data_size,
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') index_size,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
ROUND(index_length/data_length,2) index_data_ratio
FROM information_schema.TABLES
ORDER BY total_size DESC LIMIT 20;

-- Filter by table name

SELECT CONCAT(table_schema,'.',table_name) tables,
CONCAT(ROUND(table_rows/1000000,2),'M') rows_cnt,
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') data_size,
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') index_size,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
ROUND(index_length/data_length,2) index_data_ratio
FROM information_schema.TABLES
WHERE table_name LIKE "%PRIVILEGES%"
ORDER BY total_size DESC LIMIT 20;

-- Group by table schema

SELECT COUNT(*) tables, table_schema,
CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') rows_cnt,
CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') data_size,
CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') index_size,
CONCAT(ROUND((SUM(data_length+index_length))/(1024*1024*1024),2),'G') total_size,
ROUND(SUM(index_length)/SUM(data_length),2) index_data_ratio
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_size DESC LIMIT 20;

-- Data Distribution by Storage Engines

SELECT engine,
COUNT(*) tables,
CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') rows_cnt,
CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') data,
CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') idx,
CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
ROUND(SUM(index_length)/SUM(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY engine
ORDER BY SUM(data_length+index_length) DESC LIMIT 10;