The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit unique index, whose lookup is printed.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 13:13 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] 3.22 'planner omits unused left joins' SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a sqlite> INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE sqlite> INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left sqlite> join t2 using (a); 0|0|0|SCAN TABLE t1 sqlite> EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t3 using (a); 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t3 USING COVERING INDEX ndx (a=?) Why is t3 with its unique index on a not omitted from the last query plan? _______________________________________________ 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 | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
