Re: any way to speed up count(*)?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 25 Nov 2009 18:58:50 +0100
Message-ID: <7n59ivF3kq69bU1_at_mid.individual.net>
On 25.11.2009 17:19, Mark D Powell wrote:
> On Nov 24, 7:22 pm, m..._at_pixar.com wrote:
>
> 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 ?
> http://www.jlcomp.demon.co.uk/faq/count_rows.html
>
> 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.
Date: Wed, 25 Nov 2009 18:58:50 +0100
Message-ID: <7n59ivF3kq69bU1_at_mid.individual.net>
On 25.11.2009 17:19, Mark D Powell wrote:
> On Nov 24, 7:22 pm, m..._at_pixar.com wrote:
>> I've got an application being put together with cake/php. It's >> pretty nice, but their data pager does this: >> >> SELECT COUNT(*) AS COUNT >> 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 ?
> http://www.jlcomp.demon.co.uk/faq/count_rows.html
>
> 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
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Wed Nov 25 2009 - 11:58:50 CST