s.breith...@staubli.com wrote: > So when I have a query over serveral tables normally the primay key > is set as index when the table is joined.
Only if you actually join on primary key column(s). Doing so is common, but not mandatory. > If I need to add a where > clause > to a field of the joined table no single index on that field can be > used. SQLite may choose to join in a "reverse" order. Consider: select * from A join B on A.b_id = B.id where B.x = 42; One way to satisfy this query would be to scan all rows in A, for each such row look up matching rows in B using the primary key (assuming B.id is in fact its primary key), and look for those where B.x=42 (an index on B(id, x) could be useful here). Alternatively, one could scan all rows in B looking for those with B.x=42 (possibly using an index on B(x) ), and for each such row look up a matching row in A (possibly using an index on A(b_id) ). SQLite engine decides which plan is better, depending on which indexes exist. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
