>So if I build a view that includes look-ups in other tables, the >optimizer may skip these at places where not selected. However only if >the look-ups are written as outer joins. Then it may be good practice >allways doing that. For instance:
>create view vtrack as >select trackname, artistname >from track >left join artist ON trackartist=artistid -- note left join ; ~ ~ Yeah, I was thinking always to use left joins when it doesn’t make any difference. I’m using a C++ wrapper for SQLite I wrote myself. It contains a QueryGrid type that automatically splits the SQL into RowSQL and ColSQL as described earlier. It is still possible to build a vector of BaseTbl.RowIDs when the original query contains inner joins provided all columns of the unique index used in the join are satisfied (see ***Example). If it’s unable to do that (due to 1 to many joins) it computes ‘select count(*) from .....’ and sets ColSQL to ‘select ColList from ... limit ?1, ?2’ so that it still fits in with the ‘fetch a range of data’ modus operandi and also allows a record counter and vertical scrollbar positioning. ***Example select ColList from BaseTbl left join Tbl_1 on Tbl_1.X = BaseTbl.X Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y left join Tbl_3 on Tbl3.Z = BaseTbl.Z where BaseTbl.Col=? Provided Tbl_2 has unique index on Y the RowID list could be had from Select BaseTbl.RowID from BaseTbl left join Tbl_1 on Tbl_1.X = BaseTbl.X Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y left join Tbl_3 on Tbl3.Z = BaseTbl.Z where BaseTbl.Col=? which the SQLite query optimiser would presumably reduce to Select BaseTbl.RowID from BaseTbl Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y where BaseTbl.Col=? I’ve still to write code to check the query contains no joins that are ‘1 to many’. I don’t suppose there’s an easy way of determining this from explain or explain query plan? At the moment all i can think of is to scrape the index name from the explain query plan Detail column, check the number of variables involved (i.e. number of ? marks) and compare with the number of columns in the index. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
