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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  2. How to Rollback RPM Upgrade of the MySQL Server
  3. Beginners Guide to MySQL Data Types
  4. How To Start And Stop MySQL Cluster
  5. How to Troubleshoot InnoDB Lock Issues
  6. MySQL: How to kill a Long Running Query using max_execution_time
  7. “Another MySQL daemon already running with the same unix socket” – error while starting MYSQL
  8. How to List Users and Privileges in MySQL
  9. How To Create a Local Yum Repository for MySQL Enterprise Packages
  10. How to Restrict MySQL User Creation with Blank Password

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright