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.
is easily readable and obvious what you are trying to do, and
is harder to read because
In short, use
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(*)?
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.
|show 3 more comments|