13

Pretty self explanatory question. Is there any reason to use one or the other?

Spencer Ruport
  • 34,865
  • 12
  • 85
  • 147
  • Both http://stackoverflow.com/questions/59322/in-sql-whats-the-difference-between-count-and-countx and http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count come up when I search. – Bill the Lizard Jan 11 '09 at 23:41
  • I did a search for "sql difference between count" and neither of those were in the first 10 results. – Spencer Ruport Jan 11 '09 at 23:50
  • @BillTheLiz, that first one is different [uses count(fixed-val), not count(column)] although it references an earlier one that does use column. In any case, the second one IS an exact match so I'm voting for close. – paxdiablo Jan 12 '09 at 01:21

5 Answers5

47

Count(*) counts all records, including nulls, whereas Count(fieldname) does not include nulls.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
6

Select count(*) selects any row, select count(field) selects rows where this field is not null.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
3

If you want to improve performance (i.e. be a complete performance Nazi), you might want to do neither.

Example:

SELECT COUNT(1) FROM MyTable WHERE ...
Charles Graham
  • 24,293
  • 14
  • 43
  • 56
  • I've always thought the same, but assumed that by now most DBMS's would automatically translate COUNT(*) into the most efficient lookup possible, given how ubiquitous that statement is. I've no evidence either for or against it though. – Gavin Aug 06 '09 at 04:51
  • I'm sure that this would probably have the same perf as COUNT(Clustered_Index_Column), since it has to actually read the CI regardless. But it was a cool trick that the DBA put in the SQL standards. That is, don't select a column by name if you don't need it. – Charles Graham Aug 06 '09 at 05:25
1

This puzzled me for a while too.

In MySQL at least COUNT(*) counts the number of rows where every (*) value in the row is not null. Just COUNTing a column will count the number of rows where that column is not null.

In terms of performance using a single column would be slightly faster,

Ross
  • 46,186
  • 39
  • 120
  • 173
  • In SQL, can you insert a row that is entirely NULLs, or must something be non-null in every row? If you can insert such a row, is it then not counted? – A. Rex Jan 11 '09 at 22:57
  • @A. Rex: Standard SQL says that COUNT(*) counts all rows, period. As for whether you can insert a row of all NULLs, you cannot if you have declared a primary key on that table. – Bill Karwin Jan 11 '09 at 23:02
  • Actually, its the opposite regarding performance. At least in MyISAM, COUNT(*) is cached and performs much faster. In InnoDB they'll perform the same. – Eran Galperin Jan 11 '09 at 23:02
  • @Bill Karwin: That's what I thought. However, that seems to disagree with @Ross's post. COUNT(*) either counts all rows, or all rows which are not entirely null. If it's possible for them to differ, COUNT(*) can only do one ... – A. Rex Jan 11 '09 at 23:08
-1

count(*) is faster if table type is MyISAM with no WHERE statement. With WHERE the speed will be the same for MyISAM and InnoDB.

Deniss Kozlovs
  • 4,761
  • 2
  • 28
  • 35