SELECT COUNT(*) vs COUNT(1) vs COUNT(ColumnName)

Who is online?  0 guests and 2 members
Home  »  Blogs  »  BradSchacht  »  SELECT COUNT(*) vs COUNT(1) vs COUNT(ColumnName)
Avg: 0/5: (0 votes)

Comments (2)

stirone said:
I'm not so sure COUNT(*) and COUNT(1) are the same. I definitely had a situation recently where COUNT(1) ran significantly faster than COUNT(*) -- I'll have to look around for that example for the details. I read somewhere that COUNT(*) lets the optimizer pick the most favorable column for providing a count. Don't know if that helps distinguish the diffs, but thought I would mention it. I'll see if I can find that COUNT(1) usage somewhere when I get back to work on Monday...
kwymore said:
I have always heard that the count function is optimized for count(*). However, I never have gotten a good explanation on what is meant by optimized exactly. It is probably what you mentioned about the optimizer picking the best candidate column. I would have thought though that doing count(1) would be marginally faster since the optimizer wouldn't even have to look over the columns at all?
Blogs RSS Feed

BradSchacht's latest blog posts

Blogs RSS Feed

Latest community blog posts