On Fri, Mar 30, 2012 at 9:42 AM, Reinco Hof <[email protected]> wrote:
> 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. > I think that in this case, SQLite is choosing the correct query plan based on what it iknows about the data in the tables. If it really is more efficient to use the index that covers more terms of the join condition, and you make that fact known to SQLite by running ANALYZE on the data, then SQLite will choose that plan. But without ANALYZE, SQLite must guess at the effectiveness of the various indices, and at the number of entries in each table, and based on those guesses, the covering index really is a better way to go. > > > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
