Often we calculate number of rows for a single table in MySQL. Sometimes you may need to get row count for all tables in MySQL, to get an estimate of its size. In this article, we will learn how to do this. There are several ways to get row count for all tables in MySQL. We will learn a couple of simple ways to do this.
How to Get Row Count For All Tables in MySQL
Every MySQL database contains information_schema database which contains information about databases, tables and columns. It contains a table called TABLES, which contains a column TABLE_NAME which contains name of each table and column TABLE_ROWS that contains number of rows for each table.
If you want the total number of rows for all tables in your database, you can run the following query. Replace your_db with your database name.
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db';
The above query does a total of all row counts. If you want a table-wise count of rows, you can use the following query instead.
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_db';
Please note, this is only an estimate for INNODB tables. You will need to get a count(*) for each table in order to get an accurate row count. But it may be time consuming.
In this article, we have learnt a couple of simple ways to quickly calculate row count for tables. It is useful if you want to quickly get an idea of how big your tables are, or which are the largest tables in your database.
Also read:
How to Get Row Count for All Tables in PostgreSQL
How to Select Every Nth Row In PostgreSQL
How to Insert Text With Single Quotes in PostgreSQL
How to Get Element’s Outer HTML Using jQuery
How to Preview Image Before Upload in jQuery