In last week’s Getting Advanced Row Counts in MySQL (Part 2) blog we employed the native COUNT() function to tally unique values as well as those which satisfy a condition. In today’s final third instalment, we’ll learn how to obtain row counts from all of the tables within a database or entire schema.
Querying the information_schema Database
You don’t have to run a count query against every table to get the number of rows. This would be tedious and likely require external scripting if you planed on running it more than once.
The INFORMATION_SCHEMA database is where each MySQL instance stores information about all the other databases that the MySQL server maintains. Also sometimes referred to as the data dictionary and system catalog, it's the ideal place to lookup information about databases, tables, the data type of a column, or access privileges.
The INFORMATION_SCHEMA “TABLES” table provides information about…what else…tables in your databases. By querying it, you can get exact row counts with a single query.
Table Counts for One Database
It’s easy enough to obtain a row count for one database. Just add a WHERE clause with the condition that the table_schema column matches your database name:
SELECTTABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';
+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| Table1 | 105 |
+------------+------------+
| Table2 | 10299 |
+------------+------------+
| Table3 | 0 |
+------------+------------+
| Table4 | 1045 |
+------------+------------+
Table Counts for the Entire Schema
Obtaining a row count for all databases within a schema takes a little more effort. For that, we have to employ a prepared statement.
Within the statement, the group_concat() function packs multiple rows into a single string in order to turn a list of table names into a string of many counts connected by unions.
Select-- Sort the tables by count
concat(
'select * from (',
-- Aggregate rows into a single string connected by unions
group_concat(
-- Build a "select count(1) from db.tablename" per table
concat('select ',
quote(db), ' db, ',
quote(tablename), ' tablename, '
'count(1) "rowcount" ',
'from ', db, '.', tablename)
separator ' union ')
, ') t order by 3 desc')
into @sql
from (
select
table_schema db,
table_name tablename
from information_schema.tables
where table_schema not in
('performance_schema', 'mysql', 'information_schema')
) t;
Our concatenated select statements are saved in the @sql variable so that we can run it as a prepared statement:
-- Execute @sqlprepare s from @sql; execute s; deallocate prepare s;
+-----+-----------+------------+
| db | tablename | rowcount |
+-----+-----------+------------+
| DB1 | Table1 | 1457 |
+-----+-----------+------------+
| DB1 | Table2 | 1029 |
+-----+-----------+------------+
| DB2 | Table1 | 22002 |
+-----+-----------+------------+
| DB2 | Table2 | 1022 |
+-----+-----------+------------+
A Final Word regarding Speed and Accuracy
These queries will perform very fast and produce extremely exact results on MyISAM tables. However, transactional storage engines such as InnoDB do not keep an internal count of rows in a table. Rather, transactional storage engines sample a number of random pages in the table, and then estimate the total rows for the whole table. The ramifications of MVCC, a feature that allows concurrent access to rows, are that, at any one point in time, there will be multiple versions of a row. Therefore, the actual count(1) will be dependent on the time your transaction started, and its isolation level. On a transactional storage engine like InnoDB, you can expect counts to be accurate to within 4% of the actual number of rows.
