Get a list of most populated tables in mysql

If you are a sysadmin and do not have a fellow mysql dba, you should learn some tricks to look for big tables or tables used very much.

This query (and modified versions of it) accomplishes the task:

mysql> SELECT table_name, table_schema, engine, table_rows, data_length FROM information_schema.tables where table_schema not in ('information_schema', 'mysql') order by table_rows desc limit 20;

This searches for every table which is not part of 'information_schema' and 'mysql' (that is every user database).

The result is similar to this one (names and schemas removed to protect the innocents ;))

+-----------------------+--------------+--------+------------+-------------+
| table_name            | table_schema | engine | table_rows | data_length |
+-----------------------+--------------+--------+------------+-------------+
|                       |              | MyISAM |    2828398 |   162470312 | 
|                       |              | MyISAM |    1609232 |   131429376 |
|                       |              | MyISAM |    1195717 |   218651496 |
|                       |              | InnoDB |     574491 |    43581440 |
|                       |              | MyISAM |     565128 |    10737432 |
|                       |              | MyISAM |     480290 |    26981100 |
|                       |              | MyISAM |     380521 |    22509908 |
|                       |              | MyISAM |     280128 |     8536752 |
|                       |              | MyISAM |     227324 |    18714388 |
|                       |              | MyISAM |     210842 |    17857596 |
|                       |              | InnoDB |     201526 |    45694976 |
|                       |              | MyISAM |     177225 |    10055608 |
|                       |              | InnoDB |     123960 |     5783552 |
|                       |              | MyISAM |     119143 |     9524852 |
|                       |              | MyISAM |      99960 |    10038104 |
|                       |              | MyISAM |      97712 |     8010128 |
|                       |              | InnoDB |      94167 |     8404992 |
|                       |              | MyISAM |      93962 |    16401252 |
|                       |              | MyISAM |      91817 |     1836340 |
|                       |              | InnoDB |      84424 |     4734976 |
+-----------------------+--------------+--------+------------+-------------+
20 rows in set (0.18 sec)