It's not a solution, because in your example, foo will be scanned until limit is reached. This may take considerable amount of time, if results are parse.
Also, this solution is totally useless. It's a partial index on 'foo', meaning that I can know true or false having only rows of 'foo'. In the other words: there is no need to do extra searches in 'partial-index-tables' like bar. The idea is that those tables are orders of magnitude smaller than foo, hence the LEFT JOIN to make it verbose and force query planner to scan 'bar' and co first. > Maybe you are looking for > > SELECT id,baz from foo where exists( select 1 from bar where foo = id) ... > > which has the effect of easily extending to an arbitrary number of bar tables > via additional exists subqueries that may be connected by logical operators > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Paul > Gesendet: Donnerstag, 17. November 2016 13:58 > An: General Discussion of SQLite Database > Betreff: [sqlite] Query Planner fails to recognise efficient strategy when > '=' condition gives a strong hint > > These are the queries: > > CREATE TABLE foo( > id INTEGER, > baz INTEGER, > PRIMARY KEY(id) > ); > > CREATE TABLE bar( > foo INTEGER, > PRIMARY KEY(foo), > FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE ); > > EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON bar.foo = > foo.id ORDER BY id LIMIT 10, 10; > > selectid order from detail > ---------- ---------- ---------- > ----------------------------------------------------------- > 0 0 0 SCAN TABLE bar > 0 1 1 SEARCH TABLE foo USING INTEGER PRIMARY > KEY (rowid=?) > 0 0 0 USE TEMP B-TREE FOR ORDER BY > > 'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be > used for 'ORDER BY id' ordering. > But it does not happen, hence the plan includes full scan of 'bar' and TEMP > B-TREE construction. > > > The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo' > is that bar pays a role of partial index. > Database was created just a year before partial indexes were released. Bar is > not a single such table, there are more. > The query is being constructed dynamically and in the end 'ORDER BY id' is > appended to ensure that query is correct no matter how many > 'partial-index-tables' the foo is LEFT JOIN-ed with. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
