MySQL get top 20 tables size from MySQL

Here is a handy little script to get the top 20 MySQL tables based on size

I was trying to free some space up on my zabbix server; the database had grown to a 6G within a few months. I needed to get the top offending table to see what kind of data was in there I could get rid of. Since I only use Zabbix mostly for at the moment monitoring and no so much reporting on things over time. I went with the history table.

TABLE	ROWS	Data	Idx	Total Size	IDX-Frac
zabbix.history_uint	36.95M	1.81G	0.97G	2.78G	0.53
zabbix.history	6.59M	0.32G	0.17G	0.50G	0.53
zabbix.trends_uint	4.23M	0.29G	0.00G	0.29G	0.00

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  20;