Re: count(*) v. count(pk)

Hi Brendan,

the issue is not whether you are counting pk but whether you are using an index or tablescan to do it.

If you do select count(pk) from table the result will be a full table scan in the same way as select count(*) from table because of the lack of a where clause.

However, if your table is indexed on a single column say this_column and then select count(*) from table where this_column > -1 will use the index to perform the count. [ If this_column is a char then it becomes this_column > ' ' ].

If only a concatenated index is available use just the first column.

regards

Rod Corderey
Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

brendan_o'brien_at_wrightexpress.com wrote:
>
> Hi folks.
>
> Saw a post recently where someone recommended doing a count(pk) instead of a
> count(*) as count(*) always does a full table scan.
>
> Questions:
>
> 1) Is this in fact true (that a count(*) always does a full table scan)?
>
> 2) Isn't counting on the primary key an impossibility if the key is comprised
> of multiple columns?
>
> 3) Can anyone suggest any other method for quickly retieving total rows from
> large, multi-column-PK tables?
>
> I tested this theory on a table with 250,000 rows and a one-column PK using
> both queries, and got no difference in response time. I can think of no way
> to test this on a table with a 2+ column PK as count() accepts only one
> argument.
>
> This isn't of prime importance, more to settle an internal debate here.
>
> Thanks,
> -Brendan
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue Apr 28 1998 - 04:42:21 CDT