COUNT(*) vs COUNT(col)

Posted on by

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.

4 thoughts on “COUNT(*) vs COUNT(col)”

  2. Transactional engines e.g. will also treat COUNT(col) and COUNT(*) differently. Other engines don’t have MyISAM’s optimisation and therefore need to actually count the rows. COUNT(*) FROM InnoDB will behave differently for performance than COUNT(non-pk) – COUNT(non-null non-pk) MIGHT be faster even in cases where it returns the same result.

    In an engine with MVCC, COUNT() returns the count of the rows visible in the current transaction, which is not necessarily the same for all current transactions.

  3. Huh, interesting! I’ve sometimes seen people do “SELECT COUNT(1)” or similar, most likely in the hopes of being more efficient. Is that really more efficient? It doesn’t seem like it would be, but perhaps it is.

  4. From memory I think it would have the same effect as COUNT(*) but try it and find out (use EXPLAIN).

Comments are closed.