subject:"\[sqlite\] LEFT JOIN optimization"

Re: [sqlite] LEFT JOIN optimization

2011-09-23 Thread Dan Kennedy

On 09/23/2011 04:01 AM, Mira Suk wrote: EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) selectidorderfromdetail 000SCAN T

Re: [sqlite] LEFT JOIN optimization

2011-09-22 Thread Mira Suk

_ > Od: "Jim Morris" > Komu: > Datum: 22.09.2011 23:06 > Předmět: Re: [sqlite] LEFT JOIN optimization > > Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is > an associated

Re: [sqlite] LEFT JOIN optimization

2011-09-22 Thread Jim Morris

Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is an associated ItemsME record so the left outer join is pointless. Just use the inner join. Normally the left outer join would include all of ItemsME_Properties, that probably explains the table scan.

[sqlite] LEFT JOIN optimization

2011-09-22 Thread Mira Suk

EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) selectid order from detail 0 0 0 SCAN TABLE ItemsME_Properties (~100 rows) 0