Is COUNT(fld) faster than COUNT(*)?

Question

Possible Duplicate:
COUNT(id) vs. COUNT(*) in MySQL

Short but simple: in MySQL, would a SELECT COUNT(fld) AS count FROM tbl be faster than SELECT COUNT(*) AS count FROM tbl as I understand * is the "all" selector in MySQL.

Does COUNT(*) select all rows to compute a count, and therefore make a query like SELECT(id) less expensive? Or does it not really matter?

Possible duplicate: http://stackoverflow.com/questions/1697137/countid-vs-count-in-mysql — Martin Schapendonk, Nov 18 '10 at 14:56
http://stackoverflow.com/questions/1697137/countid-vs-count-in-mysql — bcmcfc, Nov 18 '10 at 14:57

score 6 · Accepted Answer · answered Nov 18 '10 at 15:02

No, count(*) is faster than count(fld) (in the cases where there is a difference at all).

The count(fld) has to consider the data in the field, as it counts all non-null values.

The count(*) only counts the number of records, so it doesn't need access to the data.

score 2 · Answer 2 · answered Nov 18 '10 at 15:02

SELECT COUNT(*) AS count FROM tbl

The above query doesn't even count the rows assuming there's no WHERE clause, it reads directly from the table cache. Specifying a field instead of * forces SQL to actually count the rows, so it's much faster to use * when there's no WHERE clause.

score 1 · Answer 3 · answered Nov 18 '10 at 15:01

* is the “all” selector in MySQL

That's true when you SELECT columns, where the * is a shortcut for the whole column list.

SELECT * becomes SELECT foo, bar.

But COUNT(*) is not expanded to COUNT(foo,bar) which is nonsensical in SQL. COUNT is an aggregate function which normally needs one value per selected row.

Is COUNT(fld) faster than COUNT(*)?

3 Answers3