On 04/29/2016 09:41 PM, Josef Ku?era wrote: > Hello, > I have discovered a possible query planner improvement. I am using the SQLite > with virtual table modules. > > Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID, > B.RowID B_ROWID, A.*, B.* from A left outer join B on B.ref=A.key) V WHERE > V.B_ROWID=?', where A and B are virtual tables. If I am not mistaken the > WHERE term effectively turns the outer join to an inner join and the query > could be evaulated as two fast key searches. Unfortunately, currently the > join is left as is and the execution peforms a sequentail full-scan of the A > table with many key searches to the B table. > > Situation 2: How does SQLite evaluate tables to the left of a left join? As > it seems it is done by order in the SQL. So query like this: select * from A > join B on B.ref=A.key left outer join C on C.ref=A.key where C.Value=? causes > a full scan of A and B before even evaulating C. By looking at the query the > B table does not seem to be a pre-requisite of table C, although is it marked > as one in where trace.
At the moment, SQLite processes everything to the left of the LEFT (or CROSS) JOIN before everything to the right of it. Dan.
