Re: Nested loop Query performance on PK

Hello,

Le 26/07/09 7:09, Greg Caulton a écrit :
> On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton <caultonpos(at)gmail(dot)com
> <mailto:caultonpos(at)gmail(dot)com>> wrote:
>
> Hello,
>
> It seems to me that the following query should be a lot faster.
> This runs in 17 seconds (regardless how many times I run it)
>
> select ac.* from application_controls_view ac, refs r where
> ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%XYZ%';
> [...]
> Does this seem right to you? Anything I can tune ?
> [...]
>
> Oh it seems to be the join that is throwing it off, because this runs in
> 600 ms
>
> select ac.* from application_controls_view ac
> where ac.application_control_id in (
> 50000745,
> 50000760,
> [...]
> 50021066,
> 50020808
> )
>
> never mind, makes sense now - its fixed
> [...]

The following rewritten query may be satisfiable for the generic case of
using arbitrary LIKE pattern for refs.ref_key and performing in a short
acceptable time as well:

SELECT ac.*
FROM application_controls_view AS ac
INNER JOIN (
SELECT ref_id
FROM refs
WHERE ref_key LIKE '%XYZ%'
) AS r
ON ac.custom_controller_ref_id = r.ref_id;

The hint is to build a subquery, from refs table, and to move in the
WHERE clause that only refers to refs column (ref_key here). This
subquery results in a shorter table than the original (refs here),
thence reducing the number of joins to perform with ac (no matter
working with view or original table).

Regards.

--
nha / Lyon / France.