>CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t2 using (a); 0|0|0|SCAN TABLE t1 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? I see this was added to the commit history timeline yesterday “Update the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint.” which will hopefully solve the problem. In the meantime changing line 3 to CREATE TABLE t3 (a INTEGER NOT NULL) does result in table t3 being omitted from the explain query plan. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users