On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje Radovanovic' [email protected]
[firebird-support] <[email protected]> wrote:
>
> I tried query with subquery in where clause and found big issue for this
> type of subquery.
>
> 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 clause has priority (and there is no way to be opposite)
> than we have big improvement in optimization.
>
> Is there an index on partners.country? What plans are generated for each
query?
If I were writing this query, I'd write
select o.*
from orders o
inner join partners p
where p.partid = o.opartid
and p.country = 'Spain';
All that silliness about "select from (select from )" probably doesn't save
anything in
this case - it does when the inner select is complicated, but not here.
Firebird won't
carry around unneeded fields from the partners table.
What is the distribution of partners.country?
Good luck,
Ann
> __,_._,_
>
