Hi

I am starting think the firebird optimizer has some real problems with
left outer joins or there is something I don't understand about
indexes. As soon as you seem to use a left outer join it seems to think
it needs to do a table scan. If you run the sql below it does a table
scan of the phone table. With a inner join instead of the outer it
works correctly.

SELECT
phone.*
from phonlink INNER JOIN phone ON phone.phoneref = phonlink.phoneref
left outer join emailoptions ON phone.emailtype = emailoptions.emailtype
WHERE phonlink.loclinkref = 'LO00000004'

Phonlink has 24,000 records and has an index on loclinkref (selectivity
2.1)
Phone has 24,000 records and phoneref is unique
emailoptions has 7 records

Now I know it seems to optimize incorrectly due to the phoneref index on
phonlink but that index has a selectivity of 1.01 so it is a good index.

Cheers.