155

Does it make a difference if you do count(*) vs count(column-name) as in these two examples?

I have a tendency to always write count(*) because it seems to fit better in my mind with the notion of it being an aggregate function, if that makes sense.

But I'm not sure if it's technically best as I tend to see example code written without the * more often than not.

count(*):

select customerid, count(*), sum(price) 
from items_ordered
group by customerid
having count(*) > 1;

vs. count(column-name):

SELECT customerid, count(customerid), sum(price)
FROM items_ordered
GROUP BY customerid
HAVING count(customerid) > 1;
Deduplicator
  • 44,692
  • 7
  • 66
  • 118
bread
  • 1,619
  • 2
  • 11
  • 6

5 Answers5

207

Your use of COUNT(*) or COUNT(column) should be based on the desired output only.

InSync
  • 4,851
  • 4
  • 8
  • 30
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 5
    there is a performance difference (at least in MySQL) as well (see my answer). – nickf Jun 09 '10 at 07:51
  • 3
    To be exact, COUNT(column) counts rows in which values of column is not NULL, whereas COUNT(*) counts all rows of the table. – gyousefi Oct 03 '19 at 07:49
47

This applies to MySQL. I'm not sure about the others.

The difference is:

  • COUNT(*) will count the number of records.
  • COUNT(column_name) will count the number of records where column_name is not null.

Therefore COUNT(*) is what you should use. If you're using MyISAM and there is no WHERE clause, then the optimiser doesn't even have to look at the table, since the number of rows is already cached.

nickf
  • 537,072
  • 198
  • 649
  • 721
  • 9
    So I understand that count(col) is slower than count(*) because MySQL has to examine the rows to see if NULL, but if the column is defined as NOT NULL, then does it still matter? – Shane N Jul 02 '14 at 18:07
24

When it's an identifier (and guaranteed to be non-NULL) then it probably doesn't matter.

However, there is a difference between COUNT(*) and COUNT(column) in general, in that COUNT(column) will return a count of the non-NULL values in the column. There is also the COUNT(DISTINCT column) variant which returns the number of unique, non-NULL values.

Dean Harding
  • 71,468
  • 13
  • 145
  • 180
6

Yes, there is possible difference in performance. Depending on your query, and the indexing of the table in question, it can be quicker to get the count from the index instead of going to table for the data. Thus you probably should specify the field name, instead of using *.

Tommi
  • 8,550
  • 5
  • 32
  • 51
6

Generally it's the same, but in details AFAIK "count(*)" is better b/c "count(columnname)" forces DB to execute a little more code to lookup that column name (but not necessary though).

zed_0xff
  • 32,417
  • 7
  • 53
  • 72