16

Possible Duplicate:
Count(*) vs Count(1)

I remember anecdotally being told:

never use count(*) when count(1) will do

Recently I passed this advice on to another developer, and was challenged to prove this was true. My argument was what I was told along with when I was given the advice: that the database would only return the first column, which would then be counted. The counterargument was that the database wouldn't evaluate anything in the brackets.

From some (unscientific) testing on small tables, there certainly seems to be no difference. I don't currently have access to any large tables to experiment on.

I was given this advice when I was using Sybase, and tables had hundreds of millions of rows. I'm now working with Oracle and considerably less data.

So I guess in summary, my two questions are:

  1. Which is faster, count(1) or count(*)?
  2. Would this vary in different database vendors?
Community
  • 1
  • 1
Clarkey
  • 1,553
  • 5
  • 22
  • 34
  • 1
    1. ANSI SQL defines `COUNT(*)` should be optimised 2. This applies to most RDBMS. Anything else applies only to older versions *where stated* otherwise assume COUNT(*) is optimised. – gbn Aug 19 '11 at 17:05

7 Answers7

20

According to another similar question (Count(*) vs Count(1)), they are the same.

In Oracle, according to Ask Tom, count(*) is the correct way to count the number of rows because the optimizer changes count(1) to count(*). count(1) actually means to count rows with non-null 1's (all of them are non-null so optimizer will change it for you).

Community
  • 1
  • 1
andy
  • 2,953
  • 2
  • 24
  • 26
8

See

What is better in MYSQL count(*) or count(1)?

for MYSQL (no difference between count(*) and count(1))

Count(*) vs Count(1)

http://beyondrelational.com/blogs/dave_ballantyne/archive/2010/07/27/count-or-count-1.aspx

for MS Sql Server (no difference)

http://dbaspot.com/sybase/349079-count-vs-count-1-a.html

for Sybase (no difference)

Community
  • 1
  • 1
hatchet - done with SOverflow
  • 15,919
  • 5
  • 47
  • 57
4

In reading books specifically on TSQL and Microsoft SQL Server, I have read that using * is better because it lets the optimizer decide what is best to do. I'll try to find the names of the specific books and post those here.

Nathanial Woolls
  • 5,231
  • 24
  • 32
  • This also applied to using "exists(select * from...)" versus "exists(select field from...)" – Nathanial Woolls Aug 19 '11 at 16:28
  • `count(*)` is a common enough idiom that any optimizer should take it into account, not just SQL Server. I wish I had evidence to back that up. – Mark Ransom Aug 19 '11 at 16:28
  • @Mark Ransom: in the ANSI standard http://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-col/2512#2512 – gbn Aug 19 '11 at 16:36
3

This is such a basic query pattern, and the meaning is identical. I've read more than once that the optimizer treats them identically - can't find a specific reference right now but put this in the category of "institutional knowledge".

(should have searched first...http://stackoverflow.com/questions/1221559/count-vs-count1)

dkretz
  • 37,399
  • 13
  • 80
  • 138
2

I can only speak to SQL Server, but testing on a 5 GB table, 11 mm records - both the number of reads and execution plan were identical.

I'd say there is no difference.

Henry Lee
  • 163
  • 7
1

As far as I know using count() should be faster because when that function is called the engine counts only indexes. From another point of view probably both count() and count(1) in binary code look very similar, so there should be no difference.

Itay Grudev
  • 7,055
  • 4
  • 54
  • 86
-7
  1. count(1)
  2. No, generally speaking this will always have slightly better performance.

It would only affect if upscaled to a drastic amount but it is good practice.

MaxSan
  • 318
  • 1
  • 13
  • 3
    no and no. Myths. -1. In addition to being a duplicate, you have this on [DBA.SE](http://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-col/2512#2512) – gbn Aug 19 '11 at 16:33
  • 1
    I don't think it's correct to make the blanket statement that count(1) will always have slightly better performance. – hatchet - done with SOverflow Aug 19 '11 at 16:36
  • 2
    Do you have any documentation at all to support this violation of conventional wisdom? – dkretz Aug 19 '11 at 16:39
  • This is debunked here (answer on duplicate question) http://stackoverflow.com/questions/1221559/count-vs-count1/1221565#1221565 – gbn Aug 19 '11 at 16:42
  • 1
    Why did this get an upvote after all the comments and 5 downvotes? It's **WRONG** – gbn Aug 19 '11 at 18:52