The problem appears to be in line:

"where t1.unindex_varchar containing 'foo'"



Firebird needs read the entire t1 table do discover if the record contains
or not 'foo'. Next, it joins with table t1.

Finally you do not have connected/joined t1 with t2 so it reads 'natural'
for t1 too.



Try it:

select *
from table1 t1
join table2 t2 on t2.indexed_char14 starting with t1.indexed_char10
where t1.unindex_varchar containing 'foo' AND T1.CODE = T2.CODE



Then it Will (IF you have) the t1.code index instead of natural for t1
table. With 'containing' you always get natural reads of the entire table.



Sorry my poor english.

Fabiano.






De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de Rick Debay
Enviada em: segunda-feira, 9 de abril de 2012 14:10
Para: firebird-support@yahoogroups.com
Assunto: RE: [firebird-support] No index used for join on 'starting with'





Anyone? Without any indexes, I won't live to see this complete.

-----Original Message-----
From: firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com>
[mailto:firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> ] On Behalf Of Rick Debay
Sent: Friday, April 06, 2012 5:18 PM
To: firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com>
Subject: [firebird-support] No index used for join on 'starting with'

This query uses natural for both tables, when I expected it to use an
index for the join.

select *
from table1 t1
join table2 t2 on t2.indexed_char14 starting with t1.indexed_char10
where t1.unindex_varchar containing 'foo'

This uses an index, so the optimizer knows about it.

select *
from table2 t2
where t2.indexed_char14 starting with 'bar'

How can I get the optimizer to use the index for the join?

Disclaimer: This message (including attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender by
return e-mail and delete this message from your system. Any unauthorized use
or dissemination of this message in whole or in part is strictly prohibited.
Please note that e-mails are susceptible to change. RxStrategies, Inc. shall
not be liable for the improper or incomplete transmission of the information
contained in this communication or for any delay in its receipt or damage to
your system. RxStrategies, Inc. does not guarantee that the integrity of
this communication has been maintained nor that this communication is free
from viruses, interceptions or interference.





[Non-text portions of this message have been removed]