count(*) and count(column_name), what's the diff?

Question

in mysql.

thanks.

Answer 1

There is no performance difference between COUNT (*), COUNT (ColumnName), COUNT (1).

Now, if you have COUNT (ColumnName) then the database has to check if the column has a NULL value, and NULLs are eliminated from aggregates. So COuNT (*) or COUNT (1) is preferable to COUNT (ColumnName) unless you want COUNT (DISTINCT ColumnName)

Answer 2

• COUNT(*) counts all rows in the result set (or group if using GROUP BY).
• COUNT(column_name) only counts those rows where column_name is NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable).
• COUNT(1) is the same as COUNT(*) since 1 can never be NULL.

To see the difference in the results you can try this little experiment:

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
    COUNT(*) AS a,
    COUNT(x) AS b,
    COUNT(1) AS c
FROM table1;

Result:

a   b   c
3   2   3

Answer 3

Depending on the column definition -i.e if your column allow NULL - you could get different results (and it could be slower with count(column) in some situations as Mark already told).