jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes count(*) vs count(id) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "count(*) vs count(id) " Watch "count(*) vs count(id) " New topic
Author

count(*) vs count(id)

Tristan Van Poucke
Ranch Hand

Joined: Jun 30, 2008
Posts: 47
any difference between those two perfomance wise?
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Tristan Van Poucke wrote:any difference between those two perfomance wise?


if that is the only thing changing... no difference.
Goutham Pallipati
Greenhorn

Joined: Aug 13, 2008
Posts: 20

if you are checking whether there exists a record for given condition the I would suggest to use following instead of count.
because count often result in full table scans.


using count

Select count(1) from emp where empno=121;

using exists it becomes

select count(1) from emp where exists (select empno from emp where empno=121)
Tristan Van Poucke
Ranch Hand

Joined: Jun 30, 2008
Posts: 47
nice thank you.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Goutham Pallipati wrote:
if you are checking whether there exists a record for given condition the I would suggest to use following instead of count.
because count often result in full table scans.


using count

Select count(1) from emp where empno=121;

using exists it becomes

select count(1) from emp where exists (select empno from emp where empno=121)


I don't follow this. If there is no index on empno, both queries run the risk of a full table scan. What difference does exists make?

In fact, is this even logically correct? The first will give you a count of all emp records where empno == 121, the second will increment the count when the subquery evalues to true and not re-evaluate the exists clause which I think will give a different result (unless I'm missing something)?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Paul Sturrock wrote:
Goutham Pallipati wrote:
if you are checking whether there exists a record for given condition the I would suggest to use following instead of count.
because count often result in full table scans.


using count

Select count(1) from emp where empno=121;

using exists it becomes

select count(1) from emp where exists (select empno from emp where empno=121)


I don't follow this. If there is no index on empno, both queries run the risk of a full table scan. What difference does exists make?

In fact, is this even logically correct? The first will give you a count of all emp records where empno == 121, the second will increment the count when the subquery evalues to true and not re-evaluate the exists clause which I think will give a different result (unless I'm missing something)?


Paul,

There are even more issues with the suggestion than that...

You are correct that if empno has no index... there will be a full table scan but even more so since there isn't any correlation between the inner and outer query for the exists clause... there will be a full table scan on the outer query regardless of the presence of an index on empno in the example.

When using exists a correlation should exist between the inner and outer queries.


Exists is a Boolean and it evaluates for each row in the outer query... if the Exists clause evaluates to true, the row is returned, false if it evaluates to false. In this case, if an empno of 121 does NOT exist within the emp table... the entire table is scanned in the outer query with no rows returned.

If an empno of 121 does exist within the emp table... all rows of the emp table would be returned.

...and as you correctly pointed out... if there is no index, there is a full table scan for both the inner and outer query.

Even more to the point... the count(*) function is a useful aggregation function that avoids the need to do multiple sub-queries (with Select 1 and Group By and Sum()) to return useful counts of data occurrences. It allows you to do something in a single query that would take you at minimum 2 queries, a group by clause, and sum function to duplicate the same results. It isn't an inefficient SQL tool. It is a useful one when needed.

Paul
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: count(*) vs count(id)
 
Similar Threads
Javaranch members
Smokers on javaranch
What do you do when you get home?
lifetime of pageContext?
VAJ 3.5.4 WTE Tales of Woe