Hi Sean,

I realize that the commented-out queries are not identical. I listed them in
order to illustrate that I have all indexes set up properly. However, the
indexes do get picked up only if there is one INNER JOIN or one LEFT and one
INNER join as Zlatko suggested in the original thread.

So, I do have an index on COPY_CLASSIFICATION."COPY_ID", but it's not being
picked up.

Alec

On Mon, Jan 18, 2010 at 3:32 PM, Leyne, Sean <Sean@...>wrote:

>
>
> Alex,
>
> > I am sorry I was not clear on my objectives. The first goal is to
> > understand why commenting out either INNER join causes the query
> > to run 20 times faster even though the first query in the join
> > always returns a fixed number of rows (in this case 0 rows).
>
> First, your commented out queries are not equivalent.
>
> > The second goal is to optimize the query to run faster.
> >
> > I am using Firebird 2.1.2. Here are some query plans:
> >
> > -- Your query
> > SELECT * FROM PHYSICAL_COPY a
> > INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
> > COPY_CLASSIFICATION."COPY_ID"
> > INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID"
> > WHERE (a."REPOSITORY_TYPE" = 'blah')
> >
> > PLAN JOIN (COPY_CLASSIFICATION NATURAL, A INDEX
> > (IDX_/F13/OidwbfyhwFbjRjfng==), COPY INDEX
> > (PK_ZM6SRonqR8AHSQuCISgvnQ==))
> > 334757 fetches, 0 marks, 4209 reads, 0 writes.
> > 0 inserts, 0 updates, 0 deletes, 41609 index, 41586 seq.
> > Delta memory: -1000 bytes.
> > Total execution time: 0.829s
>
> Your problem is because you don't have an index on
> COPY_CLASSIFICATION."COPY_ID"
>
> Create one and things will be much better.
>
> Sean
>
>
>


[Non-text portions of this message have been removed]