Mastering MySQL COUNT: When to Use *, COUNT(1), or Column Names

Understanding the COUNT Function

Database queries often involve counting rows. The COUNT function is used in MySQL and Oracle to return the number of rows that satisfy a condition.

Common interview questions

How many usages does COUNT have?

Difference between COUNT(column) and COUNT(*)?

Difference between COUNT(1) and COUNT(*)?

Which is more efficient, COUNT(1) or COUNT(*)?

Why does Alibaba Java Development Manual recommend COUNT(*)?

What optimizations does MyISAM provide for COUNT(*)?

What optimizations does InnoDB provide for COUNT(*)?

What is the prerequisite for MySQL COUNT(*) optimizations?

Does adding a WHERE clause affect SELECT COUNT(*)?

How are COUNT(*), COUNT(1) and COUNT(column) executed?

COUNT syntax

COUNT(expr) returns the number of non‑NULL values of expr in the result set and yields a BIGINT. If no rows match, it returns 0.

COUNT(*) counts all rows, including those where columns are NULL.

Example table and results:

create table bla(id int, id2 int);
insert bla values(null,null);
insert bla values(1,null);
insert bla values(null,1);
insert bla values(1,null);
insert bla values(null,1);
insert bla values(1,null);
insert bla values(null,null);

Query:

select count(*), count(id), count(id2) from bla;
-- results: 7 3 2

COUNT(constant) and COUNT(*) both count rows, while COUNT(column) excludes rows where the column is NULL, making it slower.

Optimizations for COUNT(*)

COUNT(*) is defined by SQL‑92, so MySQL has many optimizations.

In MyISAM, the total row count is stored separately, allowing COUNT(*) to return the stored value instantly when no WHERE clause is present.

InnoDB cannot store a static row count because of transactional row‑level locking, but it optimizes COUNT(*) by scanning the smallest non‑clustered index when possible (MySQL 8.0.13+).

COUNT(*) vs COUNT(1)

MySQL treats SELECT COUNT(*) and SELECT COUNT(1) the same way; there is no performance difference.

Therefore, the recommended form is COUNT(*), the standard defined by SQL‑92.

COUNT(column)

COUNT(column) performs a full table scan and checks each row for NULL, so it is slower than COUNT(*) or COUNT(1).

Conclusion

Use COUNT(*) for row counting in MySQL. It benefits from engine‑specific optimizations (MyISAM stores the row count; InnoDB uses the smallest index). COUNT(1) offers no advantage, and COUNT(column) is slower due to NULL checks.