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: [email protected] [mailto:[email protected]] Em nome de Rick Debay Enviada em: segunda-feira, 9 de abril de 2012 14:10 Para: [email protected] 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: [email protected] <mailto:firebird-support%40yahoogroups.com> [mailto:[email protected] <mailto:firebird-support%40yahoogroups.com> ] On Behalf Of Rick Debay Sent: Friday, April 06, 2012 5:18 PM To: [email protected] <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]
