E.Pasma wrote >> What about changing the remaining inner join to left join > >> Select BaseTbl.RowID >> from BaseTbl >> left join Tbl_2 on Tbl2.Y = BaseTbl.Y >> where BaseTbl.Col=? > >> and see if the SQLiter optimizer now leaves Tbl_2 out from the query >> plan. It will only do that if it is not a 1-to-n join.
I replied > If Tbl_2 isn’t involved in the columns, where or order by then > changing it to left join will mean it will definitely be left out so > I don’t get what you mean E.Pasma. I’m talking bollocks again. The left join will only be omitted if it has a 1 to 1 relationship with BaseTbl so E.Pasma is correct. I’m now wondering if you omit the WHERE & ORDER BY and run the following EXPLAIN QUERY PLAN SELECT BaseTbl.RowID FROM BaseTbl left join Tbl1 on comparison_1 left join Tbl2 on comparison_2 . . left join Tbln on comparison_n then if it returns more than 1 row then this implies there’s a 1 to many relationship in the query and we can’t proceed. (At this stage I’m by no means sure of this). Otherwise the RowSQL becomes SELECT BaseTbl.RowID FROM BaseTbl jointype_1 Tbl1 on comparison_1 jointype_2 Tbl2 on comparison_2 . . jointype_n Tbln on comparison_n WHERE ... ORDER BY ... And we leave the SQLite optimiser to filter out any left joins that aren’t required. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
