Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

performance question: count(*) vs count(<columnName>)

 
manuel aldana
Ranch Hand
Posts: 308
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,

i am using count() query and having question on performance: is there a difference between using
count(*) and count(<columnName>)? * would select all columns whereas count(<column>) only selects one particular column, so i would guess that the latter is faster (a table with less column entries needs to be created before running count()).

i ran some test scripts and count(<columnName>) seems to faster, but i wouldn't bet on it for i don't know caching and internal tweaking strategies of the server. i also think it is dbms-server product specific.

so can one generally say that count(<columnName>) is faster?
 
Charbel Keyrouz
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes of course it is much faster and if you are sure that your query returns good data you can use static counter like this:

 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
manuel aldana wrote:so can one generally say that count(<columnName>) is faster?


If you need to know for a particular situation then try it and see. If you don't need to know then the question is perhaps not worth asking...
 
manuel aldana
Ranch Hand
Posts: 308
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
manuel aldana wrote:so can one generally say that count(<columnName>) is faster?


If you need to know for a particular situation then try it and see. If you don't need to know then the question is perhaps not worth asking...


i asked because i use queries like this sometimes and instead of measuring performance everytime i would then use a default count() query approach.
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
<switching_to_geek_level>
I had an Oracle performance tuning training, where we were told that count('X') is faster than count(1) is faster than count(*).
But actually, I do not care . As if my program's performance would depend on that.
</switching_to_geek_level>
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
manuel aldana wrote:i asked because i use queries like this sometimes and instead of measuring performance everytime i would then use a default count() query approach.


So measure once (or twice) and use the result as your default approach.
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A good read from AskTom on the subject.
 
krishna kanthgaru
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jan Cumps wrote:<switching_to_geek_level>
I had an Oracle performance tuning training, where we were told that count('X') is faster than count(1) is faster than count(*).
But actually, I do not care . As if my program's performance would depend on that.
</switching_to_geek_level>


i have executed plan count(*),count(1) and count('X') on a table and result was that the plan is same for all three which is quite bemusing,

i have expected count(1) to be faster but going by the "plan" they are all same.

count(1) and count(<columnName>) can give different numbers in case of null values in the column data
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
krishna kanthgaru wrote:i have expected count(1) to be faster but going by the "plan" they are all same.

... as AskTom said in that link which Jan Cumps posted, numerous times (I lost count).
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And its not just Oracle. Most RDBMSs will spot these statements are syntactically the same and use the same execution paths. Which makes me wonder Manuel, what did you do to show a difference?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic