Hello! I'm seeking some advice on improving SQLite's query planning in relation to foreign keys. Sometimes it may be useful to exploit the fact that the columns in separate tables refer to the same information.
Consider the following schema: create table a (a, b, c); create table b (a, b REFERENCES a(b), c); create index bIdx on b(b); It may be worthwhile to use the foreign key references to append the "b.b=a.b" clause to queries spanning multiple tables: explain query plan select a, b, c from a where b in (select b from b where c=''); 0|0|TABLE a 0|0|TABLE b WITH AUTOMATIC INDEX explain query plan select a, b, c from a where b in (select b from b where c='' and b.b=a.b); 0|0|TABLE a 0|0|TABLE b WITH INDEX bIdx The second query plan could prove to be more useful if the number of rows selected from table "a" is small and number of rows in table "b" is big. Each lookup into table "b" is log(n) in this case, where n is the number of rows in table "b". Obviously adding the clause can prove to be useful in certain queries and not useful in others depending on the result set sizes, but I believe that it is possible to count the estimates and choose the better strategy. Sometimes the foreign key information could be used to skip the lookup in the original table altogether. Let's look at the following query: select b from a where b in (select b from b where c=''); As long as a.b = b.b the query could actually be rewritten as: select b from b where c=''; What use cases could you think of that would benefit from use of foreign key information in query planning? What caveats could you think of? Thanks, Filip Navara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
