MySQL exposes a virtual schema called information_schema that describes every database, table, and column on the server. One of its most useful tables is TABLES, which includes a table_rows column with an approximate row count for each table. Getting row counts this way takes milliseconds, even across thousands of tables, because the server reads cached statistics instead of scanning data.
SELECT table_schema, table_name, table_rows
FROM information_schema.tables;Truncated output:
+---------------------+---------------------------------------+------------+
| table_schema | table_name | table_rows |
+---------------------+---------------------------------------+------------+
| information_schema | CHARACTER_SETS | NULL |
| information_schema | COLLATIONS | NULL |
| mydb | users | 1803456 |
| mydb | orders | 24817220 |
| mydb | audit_log | 188903412 |
+---------------------+---------------------------------------+------------+The catch: it is an estimate
For InnoDB tables, table_rows is an estimate that the optimizer uses when planning queries. It can be off by 20-50% on write-heavy tables between ANALYZE TABLE runs. If you need an exact count, fall back to SELECT COUNT(*) FROM your_table on the specific table. If you are working at scale and exactness matters less than speed (for capacity planning, shard sizing, or monitoring dashboards), information_schema.tables is the right tool. Narrow the result with WHERE table_schema = 'mydb' and ORDER BY table_rows DESC to spot your largest tables in one query.
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
