List tables with table size in MySQL

The following query will list the top 5 tables by table size in MySQL.

SELECT 
  TABLE_SCHEMA, TABLE_NAME, 
  CEIL((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS table_size_in_MiB, 
  TABLE_ROWS AS estimated_row_count, AVG_ROW_LENGTH 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'innodb', 'mysql', 'performance_schema', 'phpmyadmin', 'sys', 'tmp')
ORDER BY table_size_in_MiB DESC LIMIT 5;

Note that TABLE_ROWS is only accurate for databases using MyISAM and not InnoDB. To get the actual row counts, see this StackOverflow post: How to get row count from all tables of a schema ( without using information schema ).