MySQL count all rows in database by table

This gets a list of tables in database ‘interesting’ and how many rows are in each table. The second query just sums them up for a grand total.

mysql> use mysql;
Database changed
mysql> SELECT table_name,table_rows FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'interesting';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| a       |         29 |
| b       |          4 |
| c       |       1104 |
| d       |   55378308 |
| e       |   56925066 |
| f       |   53479079 |
| g       |   55963435 |
| h       |   53966893 |
| i       |   55839182 |
| j       |   56854816 |
| k       |   54613112 |
| l       |   55454111 |
| m       |   56133247 |
| n       |          7 |
| o       |          4 |
| p       |         18 |
| q       |         30 |
| r       |          5 |
| s       |          5 |
| t       |         68 |
| u       |         22 |
| v       |          5 |
| w       |          7 |
| x       |      66046 |
| y       |      69439 |
| z       |      87361 |
| aa      |      89519 |
| ab      |      75892 |
| ac      |      61785 |
| ad      |      64964 |
| ae      |      69905 |
| af      |      65599 |
| ag      |      69710 |
| ah      |        381 |
| ai      |        181 |
| aj      |    2285397 |
| ak      |    2272764 |
| al      |         10 |
+---------+------------+
38 rows in set (0.04 sec)

mysql> SELECT sum(table_rows) FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'interesting';
+-----------------+
| sum(table_rows) |
+-----------------+
|       559887795 |
+-----------------+
1 row in set (0.05 sec)

mysql> 

Leave a Reply

Your email address will not be published. Required fields are marked *