For a complex query you can often get a list of the base table RowIDs very quickly with a simple query and then use an array of those values (along with the carray virtual table) to retrieve sections of data from the complex query almost instantly. I've been doing this for a while but would love to find a way of doing it automatically. The problem is as follows
Suppose I have the following query SELECT ColA, ColB, ColC, .... FROM BaseTbl jointype_1 Tbl1 on comparison_1 jointype_2 Tbl2 on comparison_2 . . jointype_n Tbln on comparison_n WHERE .... ORDER BY .... and I want a list of the BaseTbl's rowid's for all records returned by the above query in the order defined by ORDER BY. I also want it in the fastest possible way. In other words I want 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 .... with all redundant tables removed. The question is, how to determine what tables are redundant? Obviously any table involved in the WHERE or ORDER BY has to be retained aa does any table acting as an intermediate to join those tables back to the BaseTbl. Of the other tables I'm thinking I can discard table_i provided it's joined to the BaseTbl by a table_i unique index and comparison_i covers all columns of that index. It's fairly obvious if jointype_i == left join but I'm thinking it works also for inner join. Can anyone give me a counter example? Anyone think of other ways? I'm also wondering if the result produced by 'explain query plan' could simplify the coding although I know use of that isn't recommended as the structure of it is not guaranteed. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
