When you ran these two queries did you restart firebird before each. This
will insure your not taking advantage of anything left in firebird's cache
from the previous query during the second query.

Bill Meaney

>Hi!
>
>I noticed a huge difference of query speed when i switched table2 and
>table3 joins in FROM clause from:
>
>select t1.stuff, t2.stuff, t3.stuff
>from table1 t1
>join table2 t2 on t2.id = t1.field4
>left join table3 t3 on t3.field1 = cf.field1 and t3.field2 = cf.field2
>where t1.field1 = :p1
>and t1.field2 = :p2
>and t1.field3 = :p3
>
>to:
>
>select t1.stuff, t2.stuff, t3.stuff
>from table1 t1
>left join table3 t3 on t3.field1 = cf.field1 and t3.field2 = cf.field2
>join table2 t2 on t2.id = t1.field4
>where t1.field1 = :p1
>and t1.field2 = :p2
>and t1.field3 = :p3
>
>First query run for 10 mins, the second for 2 secs (FB 1.5.2).
>
>As far as I understand swithcing these joins should always give
>equivivalent result set, is it so?
>
>Somehow it feels more logical to me to put all inner joins before
>left ones, but seems that I need to reconsider this :)
>
>Is there some text describing how should I arrange joins?
>
>thanks in advance!
>
>Aivar
>