Is there any difference in the performance, or the background execution behavior when counting *
, any custom field ( n
), or the primary id
in MySQL?
What exactly does *
refer to in the query, and how does it differ from those two other ways?
SELECT COUNT( * ) FROM t;
SELECT COUNT( id ) FROM t;
SELECT COUNT( n ) FROM t;
UPDATE:
Assume, that neither id
nor n
is null at any record.
COUNT(*) will include NULLS
COUNT(column_or_expression) won't.
This means COUNT(any_non_null_column)
will give the same as COUNT(*)
of course because there are no NULL values to cause differences.
Generally, 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")
Case:
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 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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments