Differences and Performance of count(1), count(*), and count(column) in MySQL

When a table has a larger amount of data, analyzing the table shows that count(1) can take slightly longer than count(*), but the difference is minimal.

From the execution plan, count(1) and count(*) behave the same; after analysis, count(1) may be a bit faster for data under 10,000 rows, especially when the counted column is a clustered index.

Because the optimizer automatically maps count(*) to an appropriate column, there is generally no need to prefer count(1) over count(*); they are effectively equivalent.

2. count(1) vs count(column)

count(1) counts all rows, including rows where the column is NULL; count(column) counts only rows where the column is NOT NULL.

3. count(*) vs count(1) vs count(column) differences

Execution results: count(*) counts all rows regardless of NULL values; count(1) also counts all rows; count(column) ignores NULL values for that column.

Performance: if the column is a primary key, count(column) is fastest; if not, count(1) can be faster than count(column). When a table has multiple columns and no primary key, count(1) outperforms count(*). The optimal choice is count(primary_key) when a primary key exists, otherwise count(*) for single‑column tables.

4. Example analysis

mysql> create table counttest(name char(1), age char(2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into counttest values
-> ('a', '14'),('a', '15'), ('a', '15'),
-> ('b', NULL), ('b', '16'),
-> ('c', '17'),
-> ('d', null),
->('e', '');
Query OK, 8 rows affected (0.01 sec)

mysql> select * from counttest;
+------+------+
| name | age  |
+------+------+
| a    | 14   |
| a    | 15   |
| a    | 15   |
| b    | NULL |
| b    | 16   |
| c    | 17   |
| d    | NULL |
| e    |      |
+------+------+

mysql> select name, count(name), count(1), count(*), count(age), count(distinct(age))
-> from counttest
-> group by name;
+------+-------------+----------+----------+------------+----------------------+
| name | count(name) | count(1) | count(*) | count(age) | count(distinct(age)) |
+------+-------------+----------+----------+------------+----------------------+
| a    | 3           | 3        | 3        | 3          | 2                    |
| b    | 2           | 2        | 2        | 1          | 1                    |
| c    | 1           | 1        | 1        | 1          | 1                    |
| d    | 1           | 1        | 1        | 0          | 0                    |
| e    | 1           | 1        | 1        | 1          | 1                    |
+------+-------------+----------+----------+------------+----------------------+