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

Many developers mistakenly believe that COUNT(1) is faster than COUNT(*) because the former supposedly avoids a full table scan; in reality both functions trigger a full scan and their performance is virtually identical, while COUNT(column) counts only rows where the specified column is not NULL.

1. COUNT(1) and COUNT(*)

When the table contains a large amount of data, analysis shows that COUNT(1) may take slightly longer than COUNT(*), but the execution plans are the same. If COUNT(1) uses a clustered index (e.g., the primary key), it can be marginally faster, though the difference is minimal. The optimizer automatically treats COUNT() as COUNT(1), so there is no need to prefer one over the other.

2. COUNT(1) and COUNT(column)

COUNT(1)

counts every row in the table, including rows where columns contain NULL. In contrast, COUNT(column) counts only the rows where the specified column has a non‑NULL value, ignoring NULL entries.

3. Differences in execution effect and efficiency

Execution effect : COUNT(*) counts all rows without ignoring any column values; COUNT(1) behaves the same; COUNT(column) counts only the specified column and skips NULL values.

Execution efficiency :

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

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

When a table has multiple columns and no primary key, COUNT(1) outperforms COUNT(*).

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

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

The article also provides a concrete example:

mysql> CREATE TABLE counttest(name CHAR(1), age CHAR(2));
-- insert sample rows, some with NULL values
INSERT INTO counttest VALUES ('a','14'),('a','15'),('a','15'),('b',NULL),('b','16'),('c','17'),('d',NULL),('e','');
SELECT * FROM counttest;
SELECT name, COUNT(name), COUNT(1), COUNT(*), COUNT(age), COUNT(DISTINCT age) FROM counttest GROUP BY name;

The query results demonstrate that COUNT(name), COUNT(1), and COUNT(*) all return the total row count for each name, while COUNT(age) excludes NULL ages, and COUNT(DISTINCT age) counts distinct non‑NULL ages.