CODEWITHSUNDEEP
mysqlsqlperformanceselectcount
István Pálinkás
István Pálinkás

Reputation: 2352

What to count when counting all rows MySQL

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.

Upvotes: 3

Views: 356

Answers (3)

Rick James
Rick James

Reputation: 142298

For InnoDB tables, you will probably find that the "smallest" index is used for COUNT(*), COUNT(1), or COUNT(id). To see this, do EXPLAIN SELECT COUNT(...) FROM tbl;.

If you have no secondary indexes, then the 'table' must be scanned.

Note that each secondary key includes the column(s) of the PRIMARY KEY. So, for

PRIMARY KEY(a, b)
INDEX c_d (c, d)

probably any count on any of those 4 columns, or (1) or (*) will use the c_d index.

COUNT(e) will need to scan the table.

Upvotes: 1

Bashar Abutarieh
Bashar Abutarieh

Reputation: 854

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.

Upvotes: 3

Mureinik
Mureinik

Reputation: 311393

count(*) has some optimizations in certain cases (querying a single MyISAM table with no where clause), which could to be the case in the OP, depending on the storage engine. If your query doesn't hit this special case, MySQL will have to construct an execution plan and run the query normally, which would be just as good (or bad) as count(my_primary_key) if you have a primary key.

Long story short - don't over think it. Just use count(*) and let the database worry about optimizing your query. After all, that's what it's build for.

Upvotes: 1

Related Questions