4 ms·
This performance optimization is valid for most databases: COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values
by tpetry 6y ago
This performance optimization is valid for most databases:
COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values need to be checked. But in most databases there is no difference in performance because the query optimized is intelligent enough to check whether the column can have null by definition and switches to COUNT(*) if there can't be nulls. In some databases COUNT(primarykey) is even faster, but these are all optimizations most often not needed, so micro optimizations as the query optimizer is intelligent enough to choose the best logic ;)
- Zobat 6y agoI was told years ago that count(1) was faster than count(*) and have been using that blindly and without questioning since (MSSQL user), although I can't remember last time I submitted code that did that. Often write it when examining something in the database though.
- jeltz 6y agoIn PostgreSQL count(1) is slower since PostgreSQL does not optimize count(constant) to count(*). EDIT: PostgreSQL might be able to optimize that with LLVM but for short running queries it won't.
- tpetry 6y agoThese specific tricks do exist for every database. But there is no reason for mssql to not handle count(*) and count(1) the same, they are the same. Maybe they are already the same and it‘s just a very old trick which has been replaced by a better query optimizer.