The Geek Diary

CONCEPTS | BASICS | HOWTO

Counting Rows Of A Table In MySQL Server

By

Let’s understand what are the available options to get an accurate number or an estimation of rows in a MySQL table. The option to get an accurate count of rows in a table is to perform:

SELECT COUNT(*) FROM [table];

In MyISAM this operation, when no WHERE clause is provided, is very quick, as the storage engine knows at any time how many rows are in the table. When dealing with InnoDB storage engine, instead, there is no counter available. This is due to the transactional nature of this storage engine. The topic is addressed in the official documentation:

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.

Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing a smaller secondary index, if present.

Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, you can create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, SHOW TABLE STATUS can be used.

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

Therefore, starting from MySQL Server 5.7.18, it is possible to add a secondary index for an InnoDB table to improve index scanning. It is also possible to store row count in a different table and update it with a trigger (when a record is added/removed from, the trigger would add or rest “1” from the counter).

Alternatively, if a rough estimate of the row count for an InnoDB table is enough:

  • It is possible to calculate it with a scheduled task and store it in a different table.
  • Yet another approximation, would be fetching TABLE_ROWS from INFORMATION_SCHEMA.TABLES. This estimate is based on sampling random pages in the table (whose accuracy is determined by innodb_stats_persistent_sample_pages).

Additional improvement have been delivered by MySQL 8.0 in this area:

  • MySQL 8.0.13 improves performance of COUNT(*) queries on InnoDB tables (with no conditions / group by etc) since the optimizer will be allowed to choose the appropriate index to count the number of rows
  • MySQL 8.0.14 InnoDB now supports parallel clustered index reads, which can improve SELECT COUNT(*) clauses. This is achieved with innodb_parallel_read_threads configuration parameter.

You May Also Like