Performance Benefits Of COUNT(*) In SQL

This is something I often come across in code.  Just came across it in our MySLP project today , in fact.

SELECT count(id) FROM my_table

While the performance impact of that statement in itself is negligible, it does add up.  For most database engines that statement incurs additional overhead as it processes logic related to a specific column containing a specific data type.   Some database engines check if the column is null for EVERY RECORD, for example.   Not a big deal if you have an index on the column but even with an index there is overhead.

The better option is to use count(*)

SELECT count(*) FROM my_table

COUNT(*) is a semantic placeholder, NOT an operation directive in-and-of-itself.   Most database engines, MySQL included, recognize this construct and employ “shortcuts”.

Performing this operation on a moderate-sized table, for example will show a slight difference in the speed of getting the count of records in the table with count(*) almost-always pulling the desired count of records in ZERO seconds and other variants taking a second or more.

Not much, granted, but it IS a big deal with you are working in large volume instances where you are processing billions of records not thousands.  Same thing if you have thousands of tables not dozens.   A site with hundreds-of-thousands of visitors not hundreds.

The performance differential is even more pronounced when using joins in your count.

Don’t be fooled into thinking count(id) only reads one column from your database while count(*) reads ALL of them and therefore count(id) MUST be faster.  That is not how it works.  Not for most database engines.

 

Here is an article by Percona on the same subject you may be interested in:

https://www.percona.com/blog/2007/04/10/count-vs-countcol/