Things to Consider When Counting All MySQL Rows
Is there a performance difference or background behavior when counting *
, any custom field ( n
) or primary id
in MySQL?
What exactly does *
the query mean and how does it differ from the other two ways?
SELECT COUNT( * ) FROM t;
SELECT COUNT( id ) FROM t;
SELECT COUNT( n ) FROM t;
UPDATE:
Let's assume that neither id
, nor n
is meaningful in any record.
source to share
COUNT(*) will include NULLS
COUNT(column_or_expression) won't.
This means it COUNT(any_non_null_column)
will give the same as of COUNT(*)
course, because there are no NULL values to cause the differences.
Generally COUNT(*)
should be better because any index can be used, because it COUNT(column_or_expression)
cannot be indexed or SARGable
From ANSI-92 (search for "Scalar Expressions 125")
Happening:
a) If specified
COUNT(*)
, then the result is output T .b) Otherwise, let TX be a one-column table that is the result of applying T for each row and eliminating null values. If one or more null values are excluded, then the termination condition is raised: warning - null is removed in the specified function.
The same rules apply to SQL Server and Sybase at least
Note: COUNT(1)
Same as COUNT(*)
, because 1 is a non-nullable expression.
source to share
count(*)
has some optimizations in some cases (querying a single MyISAM table without a sentence where
) that the OP might have, depending on the storage engine. If your query does not fall into this special case, MySQL will have to plan the execution and run the query normally, which would be nice (or bad) as count(my_primary_key)
if you have a primary key.
In short, don't think about it. Just use count(*)
and let the database worry about optimizing your query. After all, what is it built for.
source to share
For InnoDB tables, you will probably find that for the smallest index COUNT(*)
, COUNT(1)
or is used COUNT(id)
. To see this, do EXPLAIN SELECT COUNT(...) FROM tbl;
.
If you have no secondary indexes, then you need to check the "table".
Note that each additional key contains columns (s) PRIMARY KEY
. So for
PRIMARY KEY(a, b)
INDEX c_d (c, d)
perhaps any score on any of these four columns, or (1)
or (*)
will use an index c_d
.
COUNT(e)
you will need to scan the table.
source to share