Subject Re: [firebird-support] Optimizer request
Author Louis Kleiman
Post date 2016-09-09T20:33:18Z
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' fbsupport@... [firebird-support] <firebird-support@yahoogroups.com> 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
 
 
Sent: Friday, September 9, 2016 2:30 PM
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