Lukas Eder takes on the myth that COUNT(*) differs from COUNT(1):
Now that we know the theory behind these
COUNTexpressions, what’s the difference betweenCOUNT(*)andCOUNT(1). There is none, effectively. The1expression inCOUNT(1)evaluates a constant expression for each row in the group, and it can be proven that this constant expression will never evaluate toNULL, so effectively, we’re runningCOUNT(*), counting ALL the rows in the group again.There should be no difference, and parsers / optimisers should be able to recognise this and not do the extra work of checking every expression evaluation for
NULL-ness.
Interestingly, in one of the four major RDBMS platforms (not including DB2), there is a performance difference of about 10%.
Comments closed