Understanding MySQL COUNT() Performance and Strategies for Large Tables

We start by describing a typical SMS sending system where messages are stored in an sms table with a state column indicating whether a message has been sent (0 = not sent, 1 = sent). An asynchronous thread repeatedly selects rows with state = 0 and attempts to send them.

When the number of unsent messages grows to millions, a simple SELECT count(*) FROM sms WHERE state = 0; query becomes very slow and may time out. To understand why, we examine the internal implementation of the COUNT() function.

The COUNT() function counts non‑NULL rows. MySQL consists of a server layer and a storage‑engine layer (InnoDB, MyISAM, etc.). The storage engine is chosen by the ENGINE clause in the CREATE TABLE statement.

In MyISAM the engine stores the total row count in a metadata field, so COUNT() can return the value instantly. In InnoDB the engine must traverse the smallest index tree and count leaf nodes, which requires scanning many rows for large tables.

Consequently, COUNT(*) on a large InnoDB table may require a full table scan, leading to long execution times.

We then compare different forms of COUNT :

COUNT(*) and COUNT(1) have the same performance because the server does not need to examine column values.

COUNT(primary_key) is slightly faster because the primary key is never NULL.

COUNT(indexed_column) may need a null‑check unless the column is defined NOT NULL .

COUNT(non_indexed_column) can trigger a full‑table scan and is the slowest.

The ranking is: COUNT(*) ≈ COUNT(1) > COUNT(primary_key) > COUNT(indexed_column) > COUNT(non_indexed_column)

For monitoring scenarios where an approximate row count is sufficient, the EXPLAIN command can be used. The rows field in the EXPLAIN output provides an estimated number of rows that will be examined, which is fast and good enough to detect whether the unsent‑message backlog has reached a concerning magnitude.

If an exact count is required, we can maintain a separate table that stores pre‑computed counts:

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

Updating this table inside the same transaction that modifies the original sms rows keeps the count consistent with the transaction’s isolation level, but it introduces locking overhead for high‑frequency writes.

When real‑time precision is not critical, a batch‑processing approach can be used: repeatedly fetch a fixed number of rows ordered by id , count the rows with state = 0 in each batch, and aggregate the results. This method avoids a full scan and can be scheduled daily or hourly.

Another option is to stream binlog changes to a data warehouse such as Hive and run the count query there, which offloads the work from the operational database.

In summary:

MySQL COUNT() on InnoDB tables scans rows, making it slow for large datasets.

Performance order: COUNT(*) ≈ COUNT(1) > COUNT(primary_key) > COUNT(indexed_column) > COUNT(non_indexed_column)

For approximate monitoring, use EXPLAIN … ROWS .

For exact counts, maintain a dedicated count table or update counts within the same transaction.

Batch processing or ETL to a warehouse are viable alternatives when latency requirements are relaxed.

Reference: “丁奇 MySQL 45 Lectures”.