• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Counting Rows Of A Table In MySQL Server

By admin

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. How To Create a Local Yum Repository for MySQL Enterprise Packages
  2. Understanding the REVOKE statement in MySQL
  3. How To Shut Down a Node in MySQL Cluster
  4. What is the purpose of “system user” in MySQL Replication
  5. MySQL Cluster Point-In-Time Recovery (PITR)
  6. Can MySQL Cluster Run Multiple Nodes On a Single Server
  7. MySQL: How to Set Account Resource Limits
  8. MySQL : how to set (change) user password
  9. How to Restrict MySQL User Creation with Blank Password
  10. How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary