In version 10.0 and above, COUNT( x )
is translated to COUNT(*)
if x is an expression that is known to not allow NULL. In your example, I think you have Id as a primary key (non-NULL), and therefore COUNT(Id)
is treated identically to COUNT(*) unless your Example table appears on the NULL-supplying side of an outer join.
For expressions Y
that might be NULL
, there are at least three ways that COUNT(Y)
may be slower than COUNT(*)
:
- The expression
Y
needs to be computed. This means it must be fetched from the table if it is a column; if it is a more complex expression, it needs to be evaluated to determine if it is NULL. If the COUNT(Y)
appears above a work table, the server may need to materialize Y in the work table so that it would be available at the group-by node.
- The server can not combine
COUNT(Y1)
and COUNT(Y2)
for different expressions, and materialized views can not be used unless they have an appropriate COUNT(Y)
that matches the query.
- The server can not perform semantic transformations that eliminate a table needed to compute
Y
. This also means that index-only retrieval is not possible if Y can not be recovered from the index.
I do not know of any reason why COUNT(Y)
might be faster than COUNT(*)
. So, use COUNT(*)
if you want to count rows, and COUNT(Y)
if you really need to distinguish how many rows had a NULL value for Y.
answered
10 Mar '10, 21:18
Ivan T. Bowman
2.8k●2●27●32
accept rate:
39%