1

Possible Duplicate:
COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

I want to retrieve the count from a select query.

What is faster: count(*) or count(table_field_name)?

I want to know which way is faster for performance.

Community
  • 1
  • 1
Brad
  • 12,054
  • 44
  • 118
  • 187
  • 3
    possible duplicate of http://stackoverflow.com/questions/6721944/mysql-where-clause-slower-than-full-scan/6722067#6722067 and [COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?](http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better) and http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 many others. And DBA.SE http://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-colu/2512#2512 – gbn Sep 22 '11 at 13:32

2 Answers2

5

The difference is Count(field) returns count of NOT NULL values in the field, whether COUNT(*) returns COUNT of rows. COUNT(*) in MyIsam should be faster.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

cichy
  • 10,464
  • 4
  • 26
  • 36
0

at least on MyISAM tables count(*) should be faster than count(fliedname) as it allows mysql to use an index (the primary key most times) to do the counting. if the given fieldname is the primary key, it wont make any difference.

using *, mysql wont be so dump to "load the data of the entire row" as others said - count(*) is always the fastest or one of the fastest options while count(fieldname) could be slower, depending on what field is given.

EDIT:
the documantation says:

COUNT(*) is optimized to return very quickly [...]. This optimization applies only to MyISAM tables only

read on the documentation for more information about this topic.

Important note: count(*) returns to total count of rows while count(fieldname) returns the count of rows there that given field isn't NULL. this is logically consistent as with * mysql can't know wich NULL-values to leave out. always think of this when doing count() as it may have a bic impact on the result.

oezi
  • 51,017
  • 10
  • 98
  • 115
  • Can you add a definitive article link that backs upo your first statement? – gbn 3 mins ago edit – gbn Sep 22 '11 at 13:38
  • an explanation for the downvote would be really nice. to quote the FAQ: "If you see misinformation, vote it down. Add comments indicating what, specifically, is wrong" (http://stackoverflow.com/faq#etiquette) – oezi Sep 22 '11 at 13:54
  • The downvote is because your first statement is wrong. COUNT(*) and COUNT(table_field_name) can give different results because of NULLs. – gbn Sep 22 '11 at 13:57
  • thats whats stated some lines below. but `count(*)` and `count(primary_key)` wich i was saying won't make a difference really **can't** make a difference as a primary key should't be null (and, as far as i know, a primary can't be null, even if you try). about `count(*)`/`count(normal_field)` i'm just saying `count(*)` wont ever be slower, wich is also true. – oezi Sep 22 '11 at 14:01