Querying MySQL Row Counts the Fast Way

Christian Emmer

Oct 5, 2025 · 7 min read

SELECT COUNT(*) requires an expensive full index scan, which probably isn't what you want.

I'll start with what you do want, and then explain alternatives after.

InnoDB's persistent stats

It's highly likely that you're using InnoDB as the engine for your tables as it has been the default since MySQL v5.5.5 (2010) .

MySQL v5.6.2 (2011) added the ability to persist optimizer statistics for InnoDB tables across server restarts. This behavior is controlled by the innodb_stats_persistent setting, which is "ON" by default. Individual tables can override this setting with the STATS_PERSISTENT table setting.

These table stats are persisted in the mysql.innodb_table_stats table, and can be queried like this:

SELECT n_rows
     , last_update
FROM mysql.innodb_table_stats
WHERE database_name = :databaseName
  AND table_name = :tableName;

Note: you can also find stats about indexes in mysql.innodb_index_stats.

Caveat 1: mysql.innodb_table_stats.n_rows is estimated based on sampling index pages.

InnoDB samples an innodb_stats_persistent_sample_pages number of pages (default of "20") from the clustered index and then extrapolates. Individual tables can override this setting with the STATS_SAMPLE_PAGES table setting.

As a table's size grows, so does the inaccuracy of n_rows.

Caveat 2: a table's stats may not update often.

A table's mysql.innodb_table_stats only updates in these scenarios:

"When a table undergoes changes to more than 10% of its rows" if the innodb_stats_auto_recalc setting is "ON", which it is by default
ANALYZE TABLE <table> is run
OPTIMIZE TABLE <table> is run
TRUNCATE TABLE <table> is run
Most ALTER TABLE statements, including ADD COLUMN and DROP COLUMN
CREATE INDEX and DROP INDEX

As a table's size grows, it will take more and more operations to hit that 10% threshold.

Why not `SELECT COUNT(*)`?

Because it requires a full index scan. From the official documentation :

InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.

It's worth noting that an InnoDB table will always have a clustered index because that's how it stores row data, so SELECT COUNT(*) won't ever cause a full table scan.

Because different InnoDB transactions can have different row counts, the only way to return an accurate count is to fully enumerate an index within the current transaction:

For transactional storage engines such as InnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.
InnoDB does not keep an internal count of rows in a table because concurrent transactions might "see" different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.

Full index scans can be exceptionally slow. To illustrate the problem, let's create a simple table and fill it with up to 20,000,000 rows of non-trivial size:

CREATE TABLE messages
(
    id      SERIAL PRIMARY KEY,
    message TEXT NOT NULL
);

INSERT INTO messages (message)
SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer aliquam ornare velit, auctor tempus erat ultrices ut. Phasellus ac nibh ante. Morbi consectetur, lorem in pulvinar tincidunt, augue est cursus ipsum, sed dapibus neque sapien id libero. Donec id felis sem. Morbi quis mi turpis. Nam viverra felis ac ex convallis, in congue nunc ultrices. Curabitur rutrum, lorem sit amet vulputate ultricies, velit odio ultrices dui, sed volutpat lorem felis vitae nibh. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia curae; Aenean orci mi, consectetur sed turpis sed, consequat tempor nisi. Cras id venenatis mi. Sed cursus in eros sit amet interdum.'
FROM (SELECT 1
      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
         , (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
         , (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
         , (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
         , (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e
         , (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) f
         , (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) g) temp;

You can see how the time it takes to perform a full index scan grows propotionally with the table size:

SELECT COUNT(*) FROM messages;

MySQL's OFFSET can suffer from a similar exponential cost.

The Dangers of OFFSET With MySQL

Jul 28, 2022 · 15 min read

Large limit offsets degrade the performance of most databases, but it is especially egregious in MySQL.

You cannot avoid SELECT COUNT(*) if you have a requirement for exact or realtime row counts.

`COUNT(*)` vs. `COUNT(col)`

COUNT(col) counts the number of non-null col values. If the column is indexed, then that index will be scanned, otherwise a full table scan will be necessary.

From the MySQL documentation :

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

So you should only need COUNT(col) under very specific circumstances.

`COUNT(*)` vs. `COUNT(1)`

From the MySQL documentation :

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

Why not `information_schema.tables`?

Because the table statistics columns in information_schema.tables are cached, up to a default of 24 hours (controlled by the information_schema_stats_expiry setting).

For InnoDB tables, information_schema.tables is populated from mysql.innodb_table_stats, so you should query from the source to avoid the cache. For non-InnoDB tables, you have to use information_schema.tables if you want to avoid the expensive SELECT COUNT(*).

-- Row count for every table
SELECT table_schema
     , table_name
     , table_rows
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema = :databaseName
ORDER BY table_schema, table_name;

-- Row count for a specific table
SELECT table_schema
     , table_name
     , table_rows
FROM information_schema.tables
WHERE table_schema = :databaseName
  AND table_name = :tableName;

A table's information_schema.tables statistics are updated in these scenarios:

The column's cache has expired
ANALYZE TABLE ... is run

Setting the information_schema_stats_expiry setting to "0" (zero) disables caching, causing queries against information_schema.tables to always retrieve the latest statistics from the storage engine (i.e. mysql.innodb_table_stats).

Conclusion

SELECT COUNT(*) and similar queries can take an exceptionally long time on large tables. You should strongly consider using the persistent stats stored in mysql.innodb_table_stats if possible.

databases mysql

Discuss