--- In firebird-support@yahoogroups.com, "jasajona" <jasajona@...> wrote:

>
> > Of course the real problem here is that the query is being executed
> > the wrong way. I know this because you say that using a left join
> > reduces it to 10 seconds.
>
> As I understood all problem is in execution plan. Changing join to
> left join efects oprimiser and it selects different execution plan?
>

Yes.

For example

select *
from table1 t1
join table2 t2 on (t1.id = t2.table1id)
where t2.id = 3;

The optimiser would use the PK index on table2, then look for matching
table1 recods on the join condition.

select *
from table1 t1
left join table2 t2 on (t1.id = t2.table1id)
where t2.id = 3;

This query is bad. It will return the right result, and providing
table1 is small it will run in reasonable time, but the optimiser will
start with table1 then join across to every record in table2, and only
then can it determine whether the record should be included. (It may
be able to use the PK index of table2 so not have to join at all, but
in any case, you are reading every record in table1)

> Does anyone know good online book about optimising querys?
>

I have found this one helpful:
http://community.borland.com/article/0,1410,26293,00.html
(Old and for IB, but still applicable)

I have not yet read it but looks interesting
http://blogs.teamb.com/craigstuntz/articles/IBOptimization1.aspx

Adam