COUNT(*), COUNT(1) or COUNT(column) in MySQL? Which Is Faster?

Background

During a code review the team noticed three different ways of writing SELECT COUNT in MySQL— COUNT(1), COUNT(*) and COUNT(column) —and wondered which one is preferable.

MySQL definition of COUNT

According to the MySQL documentation, COUNT(expr) returns the number of rows for which expr is not NULL. It returns a BIGINT value and has three common forms:

COUNT(column) : counts only rows where the specified column is not NULL.

COUNT(*) : counts all rows returned by the query, regardless of NULL values.

COUNT(1) : similar to COUNT(*); the constant 1 is evaluated for every row, so NULL values are not ignored.

Example query (the table contains some NULL values in the component column):

SELECT COUNT(*), COUNT(1), COUNT(component) FROM worklog;

Analysis of the three forms

COUNT(*) includes all rows, so it does not ignore NULL values.

COUNT(1) also includes all rows because the constant expression is always true; it likewise does not ignore NULL values.

COUNT(column) counts only non‑ NULL values of the specified column, so rows where the column is NULL are excluded.

COUNT(*) vs. COUNT(1)

On the MyISAM storage engine MySQL stores the total row count, so COUNT(*) can be answered from that metadata, making it very fast. InnoDB, which uses MVCC and row‑level locking, cannot cache the exact row count; each transaction must compute the count, but the optimizer still treats COUNT(*) and COUNT(1) the same way, so there is no measurable performance difference between them.

COUNT(column) performance

Counting a specific column requires a full table scan and a NULL check for each row, which makes it slower than COUNT(*) or COUNT(1).

Summary and recommendation

Overall, COUNT(1) ≈ COUNT(*) > COUNT(column). Because COUNT(*) is defined by the SQL‑92 standard and MySQL has many optimizations for it, the recommended practice is to use COUNT(*) for row counting.

MySQL engine optimizations

MyISAM uses a table‑level lock and stores the total row count, allowing COUNT(*) to return the cached value instantly.

InnoDB uses row‑level locks and MVCC. When counting rows, the optimizer chooses the smallest non‑clustered index to scan, avoiding a full table scan and improving performance.

Efficient existence check

When the goal is only to know whether any rows satisfy a condition, avoid COUNT(*). Use a query such as:

SELECT 1 FROM test_ucsyncdetail WHERE comid > 520 LIMIT 1;

Then check whether the result is non‑null in application code. This stops the scan after the first matching row, saving time.