Hi Andreas!

> d) A left join
>
> select t1.s1 from t1 left join t2 on t1.s1=t2.s1
>
> FlameRobin reports that the following plan has been used:
>
> PLAN JOIN (T1 NATURAL, T2 INDEX (IDX_T12))
>
> Yes, the optimizer takes the second index, but not the first one.

NATURAL is quickest possible way to access a table in its entirety. If you'd tried

select t1.s1 from t1

then the plan would have been PLAN(T1 NATURAL)

When you have a WHERE or a JOIN clause, then you're normally not interested in all the records (in your case your only interested in the records matching on s1) and using indexes for finding the records of interest may make things a lot quicker.

>e) A left join with a plan
>
>An attempt to suggest the usage of the index of the first table as well
>
>select t1.s1 from t1 left join t2 on t1.s1=t2.s1 PLAN JOIN (T1 INDEX (IDX_T11),T2 INDEX (IDX_T12))
>
>fails with
>
>Message: isc_dsql_prepare failed
>
>SQL Message : -284
>index @1 cannot be used in the specified plan
>
>Engine Code : 335544642
>Engine Message :
>index @1 cannot be used in the specified plan
>
>Does Firebird not use an index for "the left table" of a left outer join?

As it said, it cannot use an index, or at least, it cannot benefit from using it. Add 'WHERE s1.ID = ...' to your query, and an index would be used. With LEFT JOINs, the LEFT table will come before the RIGHT table in the PLAN, with INNER JOINs the optimizer can choose which to put first. In your particular example, that means that with LEFT [OUTER] JOIN, then T1 will always be NATURAL, whereas with [INNER] JOIN, then the optimizer may choose whether T1 or T2 should be NATURAL and use an index for the other.

So using NATURAL is the best you can get in this situation. If you have millions of records in the tables, then I'd strongly recommend a WHERE clause for the LEFT table (T1), and then you'd hopefully see indexes being used for both tables.

HTH,
Set