Re: count(*) ?

From: shakespeare <>
Date: Thu, 17 Jan 2008 11:42:42 +0100
Message-ID: <478f3122$0$85785$>

"Frank van Bortel" <> schreef in bericht news:ee096$478f27b0$524b5c40$
> shakespeare wrote:

>> "Frank van Bortel" <> schreef in bericht
>> news:7767f$478cff47$524b5c40$
>>> nick wrote:
>>>> I understand that when you feed the count( ) function an asterisk as
>>>> an argument it runs
>>>> slower than if you use a column name as an argument. Can someone tell
>>>> me why this is so?
>>>> Thanks
>>> Test it - it is not so - who do you believe?!?
>>> -- 
>>> Regards,
>>> Frank van Bortel
>>> Top-posting in UseNet newsgroups is one way to shut me up
>> O yes it is! If you have a sparse column (say col_a), indexed in a very
>> large table, count(col_a) will use the index , where count(*) doesn't 
>> (and
>> they will return different values as well).
>> I think you confused this with select (*) and select (1) (which perform 
>> the
>> same, although you might find a DBA at your current working place who is
>> convinced that count (8) is faster.....). They ARE not the same though, 
>> for
>> count(*) from table in a view will not invalidate a view when a column is
>> added to the table, where count(1) will.....
>> Shakespeare

> No I was not confused, though have to admit I misread the
> column name bit.
> The title had the start of another count(1) (or count(42),
> for that matter) is better (or worse) that count(*).

> That myth I wanted to stop right here.

> As far as the count(col) vs count(*) myth:

> Of course, assuming you want to know how many
> entries your table has (so not hanky-panky with
> nullable columns!)
> --

> Regards,
> Frank van Bortel

> Top-posting in UseNet newsgroups is one way to shut me up

Sorry, that's what I meant too, but I wrote "select" where I should have written "count".
And about hanky panky: it's nullable columns that make the difference in count(column) and count(*) (different speed, different results). The "myth" article is about count(1), count(*) and count (rowid) and things like that.


Shakespeare Received on Thu Jan 17 2008 - 04:42:42 CST

Original text of this message