Understanding MySQL COUNT: count(*), count(1), and count(column) Differences

1. count(1) and count(*)

When the table contains a large amount of data, analyzing the table shows that count(1) can take slightly longer than count(*), although the execution plans are the same. After analysis, count(1) may be a bit faster for tables under 10,000 rows, but the difference is minimal.

If count(1) uses a clustered index (e.g., the primary key), it can be faster, but overall the gap is small. The optimizer automatically maps count() to the appropriate column, so using count(*) is sufficient; SQL will handle the optimization.

2. count(1) and count(column)

The main differences are:

count(1) counts all rows, including those where a column is NULL.

count(column) counts only rows where the specified column is NOT NULL, ignoring NULL values.

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

Execution effect:

count(*) counts all columns (i.e., rows) and does not ignore NULL values.

count(1) also counts all rows and does not ignore NULL values.

count(column) counts only the specified column and ignores rows where that column is NULL.

Execution efficiency:

If the column is a primary key, count(column) is faster than count(1).

If the column is not a primary key, count(1) is faster than count(column).

When a table has many columns and no primary key, count(1) performs better than count(*).

If a primary key exists, counting the primary key column yields the best performance.

For a single‑column table, count(*) is optimal.

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                    |
+------+-------------+----------+----------+------------+----------------------+