Understanding MySQL COUNT(): Usage, Differences, and Optimizations

Database queries are familiar to most developers, and the COUNT function is a core part of CRUD operations, especially for row‑count statistics.

The article begins with ten interview‑style questions about COUNT, such as the different usages of COUNT, the distinction between COUNT(*) and COUNT(1), and why the Alibaba Java Development Manual recommends COUNT(*).

COUNT Basics

According to the MySQL documentation, COUNT(expr) returns the number of rows where expr is not NULL, yielding a BIGINT result. If no rows match, it returns 0. Notably, COUNT(*) includes rows with NULL values.

Example table creation and data insertion:

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

Running the query:

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

Beyond COUNT(*) , you can use COUNT(1) or COUNT(constant) to count rows, but COUNT(column) only counts rows where the column is not NULL, making it slower.

Differences Between COUNT Variants

COUNT(constant) and COUNT(*) both count every row because the constant and the whole row are never NULL. COUNT(column) may exclude rows with NULL values, so its result can differ and its performance is lower due to the extra NULL check.

MySQL treats COUNT(*) and COUNT(1) identically; there is no performance difference, as confirmed by the official documentation:

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

Therefore, the recommended practice is to use COUNT(*) , the SQL‑92 standard syntax that MySQL optimizes heavily.

MySQL Optimizations for COUNT(*)

MyISAM stores the total row count in the table metadata, allowing COUNT(*) to return the value instantly when no WHERE clause is present. This works because MyISAM uses table‑level locks, preventing concurrent modifications that would invalidate the cached count.

InnoDB cannot cache the total row count due to row‑level locking and transactions, but it still optimizes COUNT(*) by scanning the smallest possible index. When a table has a non‑clustered index, InnoDB prefers that index for the scan, reducing I/O compared to scanning the clustered (primary key) index.

These optimizations apply only when the query lacks WHERE, GROUP BY, or other filtering clauses.

COUNT(1) vs. COUNT(*)

Both forms are executed the same way in InnoDB, so there is no speed advantage for either. The article advises using COUNT(*) for readability and standards compliance.

COUNT(column) Performance

Counting a specific column requires checking each row for NULL, making it slower than COUNT(*) or COUNT(1) .

Conclusion

The article summarizes that COUNT(*) is the preferred method for row‑count queries because it follows the SQL‑92 standard, benefits from extensive MySQL optimizations (metadata caching in MyISAM and index‑based scans in InnoDB), and avoids the extra NULL checks required by COUNT(column) . Use COUNT(*) for accurate and efficient row counting.