Does COUNT(1) Really Outperform COUNT(*) in MySQL? The Truth Revealed

Many developers hear that COUNT(1) is faster than COUNT(*) in MySQL, but the reality is different; this article investigates the claim.

For MyISAM tables the total row count is stored, so SELECT COUNT(*) can return the value instantly. Since MySQL 5.5 the default engine is InnoDB, which uses MVCC and cannot cache the exact row count, so each count query must scan rows.

The official MySQL documentation states that SELECT COUNT(*) and SELECT COUNT(1) are processed identically, with no performance difference.

According to the SQL standard, COUNT(expr) returns the number of non‑NULL values of expr in the result set, while COUNT(*) counts all rows regardless of NULLs. Because the constant 1 is always true, COUNT(1) behaves the same as COUNT(*).

InnoDB may optimise the count by using the smallest secondary index; if none exists it falls back to the clustered index, reducing I/O overhead. COUNT(column) must read each row, check the column for NULL, and may need an extra lookup for the primary key, making it slower. The performance ordering is: COUNT(*) = COUNT(1) > COUNT(primary key) > COUNT(column).

Because full‑table counts can be expensive, it is advisable to cache predictable count results using Redis or a dedicated MySQL count table, while being mindful of consistency.

Understanding these nuances helps you avoid unnecessary full scans and design more efficient database queries.