Faster count?:

Author: Raster, Tim
Short Link: http://www.houseoffusion.com/lists.cfm/link=m:6:578:1970

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(*).



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 Messageno...its slower. Always count an indexed column if
possible.

----- Original Message -----
From: Michael Dinowitz <mdinowit@gmail.com>
Date: Tue, 13 Jul 2004 14:24:59 -0400
Subject: Faster count?
To: SQL <sql@houseoffusion.com>

Is count (*) faster than count(specific_field)?
Thanks
--
Michael Dinowitz
http://www.houseoffusion.com
For all your ColdFusion needs________________________________