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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
COUNT(any_non_null_column) will give the same as
COUNT(*) of course because there are no NULL values to cause differences.
COUNT(*) should be better because any index can be used because
COUNT(column_or_expression) may not be indexed or SARGable
From ANSI-92 (look for "
Scalar expressions 125")
a) If COUNT(*) is specified, then the result is the cardinality of T.
b) Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function.
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.
select count(*) from any_table
is easily readable and obvious what you are trying to do, and
select count(any_non_null_column) from any_table
is harder to read because
any_non_null_columnreally is enforced as
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:
SELECT COUNT(1) FROM tblname WHERE 1 = 1; SELECT COUNT(*) FROM tblname WHERE 1 = 1; SELECT COUNT(column-name) FROM tblname WHERE 1 = 1;
This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.