Navicat Blog

April 10, 2018 by Robert Gravelle

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:

SELECT
    TABLE_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 @sql
prepare 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.

Navicat Blogs
Feed Entries
Blog Archives
Share