Is count(*) Really the Slowest? MySQL COUNT Performance Debunked

Which COUNT variant is fastest?

Many developers assume COUNT(*) is the slowest because SELECT * FROM t reads all columns. The article disproves this myth by examining the internal execution of MySQL COUNT functions.

What does COUNT() do?

COUNT() is an aggregate function that counts rows where the argument is not NULL. For example: SELECT COUNT(name) FROM t_order; This counts rows where the name column is not NULL.

Execution of COUNT(1)

When the argument is the constant 1, MySQL treats it as always non‑NULL, so the server increments a counter for every row read. If the table has only a primary‑key index, InnoDB scans the clustered index; if secondary indexes exist, the optimizer chooses the smallest key_len secondary index, avoiding reading column values.

Execution of COUNT(*)

MySQL converts the * argument to 0, making COUNT(*) functionally identical to COUNT(1). Therefore, their performance is the same.

Execution of COUNT(column)

Counting a non‑indexed column requires reading that column’s value for every row, leading to a full table scan and the worst performance among the variants.

SELECT COUNT(name) FROM t_order;

Summary of performance

When a secondary index exists, MySQL uses it for COUNT(1), COUNT(*), and COUNT(primary_key), making them similarly fast. Creating a secondary index for these queries improves speed. Avoid COUNT(column) unless the column is indexed; otherwise it forces a full scan.

Why does MySQL need to scan?

In the InnoDB engine, row counts are not stored as a single meta value because MVCC makes the exact number nondeterministic across concurrent transactions. In contrast, MyISAM keeps a row_count in its metadata, allowing COUNT(*) to run in O(1) time.

Optimizing COUNT(*) on large tables

For very large tables, repeatedly executing SELECT COUNT(*) can be costly (e.g., a 12‑million‑row table took about 5 seconds). The article suggests two alternatives:

Approximate counts : Use SHOW TABLE STATUS or EXPLAIN to read the estimated rows value, which is fast because it does not actually scan the table.

Separate counter table : Maintain a dedicated table that stores the row count and update it on every INSERT or DELETE, providing an O(1) lookup at the cost of extra write logic.