Is count(*) Really the Slowest? MySQL Count Performance Explained

What COUNT() Does

COUNT() is an aggregate function whose argument can be a column, a constant, or any expression. It returns the number of rows where the argument is not NULL. For example, select count(name) from t_order; counts rows whose name is not NULL.

Execution of COUNT(1) and COUNT(*)

When the argument is a constant ( 1) or the wildcard ( *), InnoDB treats both the same. The server loops over each row in the chosen index, increments an internal counter, and never reads the actual column values because the constant is never NULL. If a secondary index exists, the optimizer prefers the smallest‑key‑length secondary index; otherwise it scans the clustered primary index. The official MySQL 5.7 manual states that InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) in the same way with no performance difference.

Execution of COUNT(primary‑key)

Counting the primary‑key column follows the same path as COUNT(1) and COUNT(*), except the server must read the primary‑key value to check for NULL. Since primary‑key columns are never NULL, the extra step adds negligible overhead.

Execution of COUNT(column)

When the argument is a non‑indexed column, MySQL must perform a full table scan because it cannot use an index to locate non‑ NULL values. This makes COUNT(column) the slowest variant, as demonstrated by the example select count(name) from t_order; which reads every row.

InnoDB vs. MyISAM

InnoDB must traverse rows to compute COUNT() because it cannot rely on a single stored row count due to MVCC and transaction isolation. MyISAM, however, stores a meta row_count value and can return COUNT(*) in O(1) time when no WHERE clause is present.

Practical Optimizations

Use secondary indexes : Ensure a small secondary index exists so the optimizer scans the minimal index for COUNT(*), COUNT(1), or COUNT(primary‑key).

Avoid COUNT(column) : If you need to count non‑ NULL values of a column, add an index on that column.

Approximate counts : For large tables where exact numbers are unnecessary, use SHOW TABLE STATUS or EXPLAIN to read the estimated rows value.

Maintain a separate counter table : Insert a row into a dedicated counter table on every INSERT/DELETE to obtain an exact count without scanning.

Conclusion

COUNT(1), COUNT(*), and COUNT(primary‑key) have comparable performance when a secondary index is available; COUNT(column) is the worst due to full scans. Choosing the right index strategy or using approximation techniques can dramatically reduce the cost of counting rows in large InnoDB tables.