-----Original Message-----
From: Raster, Tim [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 4:31 PM
To: SQL
Subject: RE: Faster count?
Interesting debate, eh?
Here are my (more detailed) results on 9.08 million rows in TableName
with 100 iterations each:
Key:
FieldName1 = integer, indexed aid
FieldName2 = integer, not indexed plus4
FieldName3 = integer, not indexed stateid
x, y = integer constants
Green, Yellow, Red, Grey = First, Second, Third, Fourth place times
Paraphrased SQL
Description
Results
Select _____
>From TableName
straight row count
Count(*) = 2.3330342 seconds
Count(FieldName1) = 6.5305333 seconds
Count(Fieldname2) = 7.2271000 seconds
Count(Fieldname3) = 2.2319333 seconds
Count(*) is big winner
Select _____
>From TableName
Where FieldName3 = x
straight row count, 940k rows allowed through by where clause
Count(*) = 2.9682333 seconds
Count(FieldName1) = 3.0871666 seconds
Count(FieldName2) = 3.6076333 seconds
Count(FieldName3) = 3.0338666 seconds
Count(*) decent winner.
Select _____
>From TableName
Where FieldName2 between x and y
straight row count, 1.3 million rows allowed through by where clause
Count(*) = 3.3613666 seconds
Count(FieldName1) = 4.0607000 seconds
Count(FieldName2) = 4.8925000 seconds
Count(Fieldname3) = 3.7892000 seconds
Count(*) decent winner.
Select _____
>From TableName
Where FieldName1 between x and y
straight row count, 855k rows allowed through by where clause
Count(*) = 0.0364333 seconds
Count(FieldName1) = 0.0359333 seconds
Count(FieldName2) = 2.6652333 seconds
Count(FieldName3) = 0.3201333 seconds
Count(FieldName1) very slight winner only when counted field name
matches where clause (takes advantage of index rules).
Count(FieldName2) HUGE loser, so Count(*) would be much better way to go
if where clause fieldname not indexed.
It seems to me that Count(*) is either a clear winner, or in relatively
few cases, basically tied for the best performance. So, if you need to
count rows (rather than values), I'd stick with Count(*).
-----Original Message-----
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 14:00
To: SQL
Subject: Re: Faster count?
I just ran a test (200 itterations) on a table with 5,801,195 records.
The average for count(id) was: 98.804
The average for count(*) was: 101.22
If you look in QA they actualy run the same exact execution. count(*)
exectued on object ref_id_idx which is the colum I used in the
count(id). So atleast in my example count(id) and count(*) are the
same exact thing.
Adam
----- Original Message -----
From: Raster, Tim <[EMAIL PROTECTED]>
Date: Tue, 13 Jul 2004 13:31:48 -0500
Subject: RE: Faster count?
To: SQL <[EMAIL PROTECTED]>
Actually, I think count(*) is faster. It is a special signal to the
server that it doesn't have to count field values, but rows, which saves
time having to evaluate rows. Unlike select *, it does NOT mean all
fields... it means all rows. So count(*) is faster.
-----Original Message-----
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 13:28
To: SQL
Subject: Re: Faster count?
no...its slower. Always count an indexed column if possible.
----- Original Message -----
From: Michael Dinowitz <[EMAIL PROTECTED]>
Date: Tue, 13 Jul 2004 14:24:59 -0400
Subject: Faster count?
To: SQL <[EMAIL PROTECTED]>
Is count (*) faster than count(specific_field)?
Thanks
--
Michael Dinowitz
http://www.houseoffusion.com
For all your ColdFusion needs________________________________
________________________________________________________________
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
