How to find the largest tables in MySQL

The largest tables are often the most promising candidates for optimization.
Here is a query to list top 5 largest tables in the database:

mysql> SELECT TABLE_SCHEMA,
   -> TABLE_NAME,
   -> (INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB,
   -> TABLE_ROWS
   -> FROM INFORMATION_SCHEMA.TABLES
   -> WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema")
   -> ORDER BY SIZE_MB DESC LIMIT 10;
+-----------------+----------------------------+----------+------------+
| TABLE_SCHEMA    | TABLE_NAME                 | SIZE_MB  | TABLE_ROWS |
+-----------------+----------------------------+----------+------------+
| drupal_drp1     | dr_watch                   | 429.1360 |     993763 |
| drupal_drp1     | dr_cache_page              | 331.7897 |      28577 |
| wordpress_wp    | wp_postmeta                |  73.8534 |     135896 |
| wordpress_web   | wp_redirection_logs        |  71.1273 |     239983 |
| wordpress_hr    | wp_postmeta                |  66.6717 |     117695 |
+-----------------+----------------------------+----------+------------+

5 rows in set (3.39 sec)

The statement above just makes use of the table metadata available from the table INFORMATION_SCHEMA.TABLES and displays it in a readable way.