Differences Between count(1), count(*), and count(column) in MySQL

1. count(1) and count(*)

When the table has a large amount of data, after analysis, count(1) may take slightly more time than count(*), but the difference is minimal.

Execution plans show count(1) and count(*) behave the same; after table statistics are gathered, count(1) can be a bit faster for data under 10,000 rows, though the gap is small.

If count(1) uses a clustered index (e.g., primary key), it can be faster, but the difference is negligible.

Because the optimizer can rewrite count(*) to the appropriate column, there is no need to prefer count(1); count(*) is generally sufficient.

2. count(1) and count(column)

The main differences are:

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

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

3. count(*), count(1) and count(column) differences

Result behavior:

count(*) counts all rows regardless of NULLs in any column.

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

count(column) counts only non‑NULL values of that column.

Performance:

If the column is a primary key, count(column) is fastest.

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) can be more efficient than count(*).

If a primary key exists, counting the primary key is optimal.

For a single‑column table, count(*) is the best choice.

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','');

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