Did you try it with Firebird 1.5? The optimizer in 1.5 is smarter
than in 1.0. Left joins was always a problem in 1.0

Carlos
http://www.warmboot.com.br
FireBase - http://www.FireBase.com.br

BT> Hi

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

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

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

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

BT> Cheers.