Understanding MySQL COUNT() Performance and Optimization Strategies

This article explains why counting rows in a MySQL table can become slow when the table grows large, using the example of an SMS table where unsent messages (state=0) need to be monitored.

It first shows a simple query to obtain the number of unsent messages:

SELECT COUNT(*) FROM sms WHERE state = 0;

When the number of rows reaches millions, the query may time out because MySQL must scan many rows. The article then dives into the internal implementation of COUNT() in MySQL.

MySQL consists of a server layer and a storage‑engine layer. The storage engine (MyISAM or InnoDB) determines how COUNT() is executed. MyISAM stores a row‑count metadata field, so COUNT() is a fast metadata read. InnoDB lacks such a field; it must traverse the smallest index tree and count leaf nodes, which requires scanning many rows.

The article also discusses transaction isolation (default REPEATABLE READ) and how it prevents a single global row‑count from being reliable across concurrent transactions.

Performance ranking of different COUNT() forms is presented:

COUNT(*) ≈ COUNT(1) > COUNT(primary_key) > COUNT(indexed_column) > COUNT(non_indexed_column)

For scenarios where an approximate count is sufficient, the EXPLAIN statement can be used to read the estimated rows value.

When an exact count is required, the article suggests maintaining a separate counting table:

CREATE TABLE `count_table` ( `id` int NOT NULL AUTO_INCREMENT COMMENT 'primary key', `cnt_what` char(20) NOT NULL DEFAULT '' COMMENT 'metric name', `cnt` tinyint NOT NULL COMMENT 'count value', PRIMARY KEY (`id`), KEY `idx_cnt_what` (`cnt_what`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Updating this table within the same transaction that modifies the source data keeps the count consistent but may introduce locking under high write concurrency. For less time‑critical cases, a periodic batch job can scan the source table in chunks (e.g., 10 k rows at a time) and update the counting table, or the data can be streamed to an analytical store such as Hive for fast aggregation.

Finally, the article summarizes the key takeaways about MySQL COUNT() behavior, performance differences between storage engines, and practical ways to obtain both approximate and exact row counts.