Re: any way to speed up count(*)?

From: Robert Klemme <>
Date: Wed, 25 Nov 2009 18:58:50 +0100
Message-ID: <>

On 25.11.2009 17:19, Mark D Powell wrote:
> On Nov 24, 7:22 pm, wrote:
>> I've got an application being put together with cake/php.  It's
>> pretty nice, but their data pager does this:
>>     FROM foo f
>>     LEFT JOIN bar b
>>     ON (f.asset_group_id = b.asset_group_id)
>>     WHERE 1                    = 1
>> Any way possible to speed this up?
>> Many TIA!
>> Mark
>> --
>> Mark Harrison
>> Pixar Animation Studios

> Mark, you tune a select count like you tune any other SQL statement.
> Start with the explain plan. Here is an article on how Oracle
> performs select count.
> Is there a good way of counting the number of rows in a table ?
> The best optimization is not to perform the count. That is, if you
> are going to fetch the data anyway just go ahead and fetch it if
> possible. If you just need to know if a row exists but do not truely
> need to know how many you can use either where rownum = 1 or an exists
> subquery to cut the counting off when a row is found. That is, you
> get a count of 1 for a hit and zero where there are not hits (rows
> matching query criteria).
> In you your example why do you have where 1 = 1 ? Kind of unnecessary
> isn't it.

For the count the left join might be unnecessary as well. This would be the case if there would be at most one b.asset_group_id per f.asset_group_id. Am I missing something?

Kind regards


remember.guy do |as, often| as.you_can - without end
Received on Wed Nov 25 2009 - 11:58:50 CST

Original text of this message