Maybe I'm missing something, but... ORDER BY id
Is ordering by the ID the right-hand side of a LEFT join. As such, it depends on how NULL factors into an ORDER BY. If NULL comes first, it has to find enough records where the LEFT join fails. Yeah.. I'm probably missing something. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Thursday, November 17, 2016 8:53 AM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint 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 <sqlite-users@mailinglists.sqlite.org> 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
