How about this: select O.* from orders O join partners P on (P.partid = O.partid) where P.country = 'Spain'
On Fri, Sep 9, 2016 at 12:57 PM, 'Arno Brinkman' [email protected] [firebird-support] <[email protected]> wrote: > > > Hi, > > As others already suggested i would also prefer EXISTS, but anyway there > is missing an index on Country. > > The IN sub-select is not taking into cache and then used for the index on > orders.partid. > If you want that optimalisation then go for a derived table to join > against. > > Kind Regards, > Arno Brinkman > > > *From:* mailto:[email protected] > <[email protected]> > *Sent:* Friday, September 9, 2016 2:30 PM > *To:* [email protected] > *Subject:* [firebird-support] Optimizer request > > > > I tried query with subquery in where clause and found big issue for this > type of subquery. > > for example: > > select * from orders where orders.partid in (select partners.partid from > partners where partners.country = ‘Spain’) > > Perfomance Analysis returns me this > > partners 687660 non index reads > orders 28657 index reads > > If you analyze this result you’ll find that there is 687659 unnecessary > non index reads. If developer of optimizer accept that all queries on the > left side of where clouse has priority (and there is no way to be opposite) > than we have big improvement in optimization. > > Best regards, > > Djordje Radovanovic > > > > >
