Hi,

I move this to firebird-supoport@yahoogroups.com mailinglist

> Here is the query in SQL-92:
> SELECT DISTINCT m.name, c.currency, a.name corr
> FROM tname
> JOIN tmain m ON (m.name LIKE (tname.name || '%'))
> JOIN tcorr c ON (m.id = c.id)
> JOIN taux a on (c.id_corr = SUBSTR(a.id1, 1, 16))
> WHERE c.currency = 'USD'
> ORDER BY m.name, currency
>
> I can't get the plan for this query, but if replace to 'c.id_corr =

a.id1',

> then the plan is:
> PLAN SORT (JOIN (JOIN (C NATURAL,M INDEX (IDX_MAIN),A INDEX

(IDX_AUX)),TNAME

> NATURAL))
> If I update taux.id1 to be 16 characters, this modified query ends in 2

sec.

> (326 results)
> tcorr doesn't have an index, because I saw no difference for my queries.

Ahh yes, i missed the a.id in SUBSTR :)
Well as you can see TNAME has NATURAL and is at the end of the PLAN.
Therefore is your query slow.

If you add an index on tcorr.id_corr what happens then?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81