When Should You Use COUNT(*), COUNT(1), or COUNT(column) in MySQL?

Performance Ranking of COUNT Queries

Directly from the analysis, the ordering is:

COUNT(*) = COUNT(1) > COUNT(id, primary‑key) > COUNT(column)

Therefore, in most cases you should simply use COUNT(*). Although it may seem counter‑intuitive—because SELECT statements usually list explicit columns—SQL‑92 defines COUNT(*) as the standard way to count rows, independent of NULL handling.

The Alibaba development manual explicitly mandates not to replace COUNT(*) with COUNT(column) or COUNT(constant). COUNT(*) counts rows even when columns contain NULL, while COUNT(column) skips NULL values.

COUNT(*) vs COUNT(1) Comparison

MySQL’s official documentation states that COUNT(expr) returns the number of rows where expr is not NULL, as a BIGINT. If the result set is empty, it returns 0. Notably, COUNT(*) includes rows with NULL values.

Further documentation clarifies that InnoDB treats SELECT COUNT(*) and SELECT COUNT(1) identically—there is no performance difference.

For MyISAM tables, COUNT(*) is heavily optimized when the query involves a single table, no WHERE clause, and only counts rows. Example: SELECT COUNT(*) FROM student; This optimization relies on MyISAM storing an exact row count. COUNT(1) enjoys the same speed only when the first column is defined NOT NULL.

Consequently, for InnoDB tables COUNT(*) and COUNT(1) are equivalent; for MyISAM tables COUNT(*) is at least as fast as COUNT(1), often faster.

Meaning of the COUNT() Function

COUNT()

is an aggregate function that ignores NULL values. Its argument can be a column name or any expression.

When using COUNT(name), MySQL scans the whole table, counting rows where name is not NULL. This incurs a full‑table scan and increments an internal counter for each non‑NULL value.

According to High Performance MySQL , COUNT() serves two distinct purposes:

Counting non‑NULL values of a specific column.

Counting the total number of rows. If the expression inside COUNT() cannot be NULL, the function effectively counts rows, and COUNT(*) is the clearest way to express this.

A common mistake is to write COUNT(column) when the intention is to count rows; COUNT(*) avoids ambiguity and offers better performance.

Counting a column also requires MySQL to examine each row, and if the column is not a primary‑key index, an additional lookup (back‑table) is needed, adding I/O overhead compared to COUNT(*) or COUNT(1).

Practical Recommendations

For an exact row count, prefer COUNT(*). It is clear, performant, and conforms to the SQL‑92 standard, with MySQL providing specific optimizations, especially for InnoDB.

Avoid COUNT(column) unless you truly need the count of non‑NULL values in that column; otherwise it can lead to misunderstandings and slower queries.