Performance question about select count(*) vs select count(x_id)

Is it worth it to go and change queries that were written:
select count(*) from x_table
to
select count(x_id) from x_table

if x_id is the primary key.  I'm thinking that it's worth it because the pk will be indexed, making the count faster...but i'm not sure if it's really worth the time.  

Any advice?
I have to be compatible with Oracle and Sql Server 2000...if that makes a difference...
jl1884Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jpkempConnect With a Mentor Commented:
Oracle SQL Language Reference Manual:
"All group functions except COUNT(*) ignore nulls...
Syntax: COUNT( {* | [DISTINCT|ALL] expr} )"

COUNT(*) is never converted to COUNT(pk) because these mean different things. As Danielzt said, count(x_id) only counts rows where x_id is not null.

If x_id is the primary key (and therefore not null), then count(*) IS equivalent to count(x_id). Also, in an Oracle database there will be no difference in performance.
0
 
kaboommmCommented:
it does not matter, you can use either.
0
 
DanielztCommented:
the difference is:
count(*) will include all the rows.
count(x_id) will not include the row with a null x_id.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GulaCommented:
In fact, I believe, count(*) is converted to count(primary key) when parsing
0
 
GulaCommented:
I looked up some books, there is nothing that says count(*) is converted to count(primary key) ... my teacher said once it does..., but it says that count(primary key)will work a bit faster

hope this helps
0
 
jl1884Author Commented:
Great. I was just curious! :)  Thanks.  I'll leave the queries as the are!

0
All Courses

From novice to tech pro — start learning today.