Never use IN (subselect). Change to select * from orders where exists( select * from partners where partners.partid = orders.partid and partners.country = ‘Spain’)
2016-09-09 14:30 GMT+02:00 'Djordje Radovanovic' [email protected] [firebird-support] <[email protected]>: > > > 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 > > > > > >
