?
Solved

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

Posted on 2003-02-18
6
Medium Priority
?
657 Views
Last Modified: 2008-02-01
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...
0
Comment
Question by:jl1884
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:kaboommm
ID: 7978749
it does not matter, you can use either.
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 7978815
the difference is:
count(*) will include all the rows.
count(x_id) will not include the row with a null x_id.
0
 
LVL 1

Expert Comment

by:Gula
ID: 7978888
In fact, I believe, count(*) is converted to count(primary key) when parsing
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Expert Comment

by:Gula
ID: 7979032
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
 
LVL 5

Accepted Solution

by:
jpkemp earned 200 total points
ID: 7979546
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
 

Author Comment

by:jl1884
ID: 7982086
Great. I was just curious! :)  Thanks.  I'll leave the queries as the are!

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question