Date: 2008-03-18 07:03 Subject: COUNT(*) vs COUNT(col) Security: Public

Is there a difference? Yes there is, and it's very significant both in functionality as well as in speed.

COUNT(*) counts rows. If the underlying table is MyISAM, and there's no WHERE clause, then this is extremely fast as MyISAM maintains a row count of the entire table. Otherwise, the server just needs to count the number of rows in the result set. Which is different from....

COUNT(col) which actually counts all not-NULL values of col in the result set. So here, the server needs to iterate through all the rows, tallying for which rows col has a not NULL value. Of course, if the col is NOT NULL the server may be able to optimise this, but I'm not sure - after all it's a result set not a base table.

Anyway, there ya go. I spot this with customers, and where possible changing to COUNT(*) can often result in a serious speed improvement. Nice little trick.