Pretty self explanatory question. Is there any reason to use one or the other?
+24
A:
Count(*) counts all records, including nulls, whereas Count(fieldname) does not include nulls.
Remou
2009-01-11 22:53:55
Ah I never knew this. Thanks!
Spencer Ruport
2009-01-11 22:56:40
It does not include rows where the specific fieldname is not null
Otávio Décio
2009-01-11 22:57:05
I learned something today ...
Philippe Grondier
2009-01-11 23:33:15
+2
A:
Select count(*) selects any row, select count(field) selects rows where this field is not null.
Otávio Décio
2009-01-11 22:55:42
A:
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
2009-01-11 22:56:13
A:
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
2009-01-11 22:56:46
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
2009-01-11 22:57:48
@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
2009-01-11 23:02: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
2009-01-11 23:02:32
@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
2009-01-11 23:08:30
+2
A:
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
2009-07-31 04:49:55
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 Schultz-Ohkubo
2009-08-06 04:51:03
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
2009-08-06 05:25:46