> 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. Thank you for the perfect explanation Igor! Perhaps some information like that could be added to the documentation. Especially that SQLite decides which plan is the best on its own. > > Igor Tandetnik Best regards / Mit freundlichen Grüssen Stefan Breitholz ----------------------------------------------------------------------------------------------------------- Staeubli GmbH - Theodor-Schmidt-Str. 19 DE - 95448 Bayreuth Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126 mailto:s.breith...@staubli.com http://www.staubli.com Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl Kirschner ----------------------------------------------------------------------------------------------------------- This e-mail and any attachment (the 'message') are confidential and privileged and intended solely for the person or the entity to which it is adressed. If you have received it in error, please advise the sender by return e-mail and delete it immediately. Any use not in accordance with its purpose, any dissemination or reproduction, either whole or partial, by entities other than the intended recipient is strictly prohibited. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
