I seem to remember that (on Oracle) there is a difference between uttering
select count(*) from any_table and
select count(any_non_null_column) from any_table.
What are the differences between these two statements, if any?
From ANSI-92 (look for "
The same rules apply to SQL Server and Sybase too at least
Note: COUNT(1) is the same as COUNT(*) because 1 is a non-nullable expression.
In a recent version there is indeed no difference between count(*) and count(any not null column), with the emphasize on not null :-) Have incidentally covered that topic with a blog post: Is count(col) better than count(*)?
is easily readable and obvious what you are trying to do, and
is harder to read because
In short, use
In the book Oracle8i Certified Professional DBA Certification Exam Guide (ISBN 0072130601), page 78 says COUNT(1) will actually run faster that COUNT(*) because certain mechanisms are called into play for checking the data dictionary for the every column's nullability (or at least the first column with non-nullability) when using COUNT(*). COUNT(1) bypasses those mechanisms.
MySQL cheats for 'SELECT COUNT(1) on tblname;' on MyISAM tables by reading the table header for the table count. InnoDB counts every time.
To test whether COUNT(1) will run faster than COUNT(*) in a database agnostic way, just run the following and judge the running time for yourself:
This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.