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

[manuel aldana]

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]

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:

select count(1) from tableName    

[Paul Clapham]

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]

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]

<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]

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]

A good read from AskTom on the subject.

[krishna kanthgaru]

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]

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]

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?