One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified?
How does COUNT(…) work?
But first, let’s look into some theory. The two ways to count things are not exactly the same thing. Why?
COUNT(*)counts all the tuples in a group
COUNT(<expr>)counts all the tuples in a group for which
<expr>evaluates to something that
IS NOT NULL
This distinction can be quite useful. Most of the time, we’ll
COUNT(*) for convenience, but there are (at
least) two cases where we don’t want that, for example:
When outer joining…[Read more]