Re: any way to speed up count(*)?
Date: Wed, 25 Nov 2009 08:19:40 -0800 (PST)
Message-ID: <094c0077-9a97-4361-af5f-fb335533e659_at_c34g2000yqn.googlegroups.com>
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.
HTH -- Mark D Powell -- Received on Wed Nov 25 2009 - 10:19:40 CST