Hello,
I would like to report a sqlite problem: Covering indices are preferred over indices covering more terms in a join. This leads to a suboptimal query plan. In the example below index idx_tableB_full is used instead of idx_tableB_partial, even though the partial index covers more terms of the join statement. example: CREATE TABLE tableA(Id1 INTEGER, Id2 INTEGER, data BLOB); CREATE TABLE tableB(Id1 INTEGER, Id2 INTEGER, ParentId INTEGER); CREATE INDEX idx_tableB_full ON tableB(Id1, ParentId, Id2); CREATE INDEX idx_tableB_partial ON tableB(Id1, Id2); explain query plan SELECT str.ParentId FROM tableA obj JOIN tableB str ON str.Id1 = obj.Id1 AND str.Id2 = obj.Id2; -- 0|0|0|SCAN TABLE tableA AS obj (~1000000 rows) -- 0|1|1|SEARCH TABLE tableB AS str USING COVERING INDEX idx_tableB_full (Id1=?) note: the expected query plan would be: -- 0|0|0|SCAN TABLE tableA AS obj (~1000000 rows) -- 0|1|1|SEARCH TABLE tableB AS str USING INDEX idx_tableB_partial (Id1=? AND Id2=?) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
