I get confusing (to me) results when toying with a query that I think should use a partial index as a "covering index". This is using sqlite 3.20.1 on Arch Linux x86_64.
The examples listed below start from a new, empty database. *** Given a table and an index like this CREATE TABLE tab (x1 TEXT, x2 TEXT, x3 TEXT, x4 TEXT, PRIMARY KEY (x1, x3) ) WITHOUT ROWID; CREATE INDEX ind ON tab (x2, x4) WHERE x4 IS NULL; i get the result I would expect: The index is used as a "covering index", even though if I read the bytecode correctly there's a != NULL comparison that's not strictly necessary, but that would be just room for further optimization: sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL; selectid|order|from|detail 0|0|0|SCAN TABLE tab USING COVERING INDEX ind sqlite> EXPLAIN SELECT x1, x2 FROM tab WHERE x4 IS NULL; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 10 0 00 Start at 10 1 OpenRead 1 3 0 k(4,,,,) 00 root=3 iDb=0; ind 2 Rewind 1 9 1 0 00 3 Column 1 1 1 00 r[1]=tab.x4 4 NotNull 1 8 0 00 if r[1]!=NULL goto 8 5 Column 1 2 2 00 r[2]=tab.x1 6 Column 1 0 3 00 r[3]=tab.x2 7 ResultRow 2 2 0 00 output=r[2..3] 8 Next 1 3 0 01 9 Halt 0 0 0 00 10 Transaction 0 0 2 0 01 usesStmtJournal=0 11 Goto 0 1 0 00 *** But if I remove the (redundant) column x4 from the index, it seems to me like it's no longer used as a "covering index" for the same query, even though all the output columns are still there and the index *is* actually used: DROP TABLE tab; CREATE TABLE tab (x1 TEXT, x2 TEXT, x3 TEXT, x4 TEXT, PRIMARY KEY (x1, x3) ) WITHOUT ROWID; CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL; sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL; selectid|order|from|detail 0|0|0|SCAN TABLE tab USING INDEX ind sqlite> EXPLAIN SELECT x1, x2 FROM tab WHERE x4 IS NULL; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 14 0 00 Start at 14 1 OpenRead 0 2 0 k(2,,) 00 root=2 iDb=0; tab 2 OpenRead 1 3 0 k(3,,,) 00 root=3 iDb=0; ind 3 Rewind 1 13 1 0 00 4 Column 1 1 1 00 r[1]= 5 Column 1 2 2 00 r[2]= 6 NotFound 0 12 1 2 00 key=r[1..2] 7 Column 0 3 3 00 r[3]=tab.x4 8 NotNull 3 12 0 00 if r[3]!=NULL goto 12 9 Column 1 1 4 00 r[4]=tab.x1 10 Column 1 0 5 00 r[5]=tab.x2 11 ResultRow 4 2 0 00 output=r[4..5] 12 Next 1 4 0 01 13 Halt 0 0 0 00 14 Transaction 0 0 47 0 01 usesStmtJournal=0 15 Goto 0 1 0 00 *** It gets even more confusing: If I remove the "TEXT" type from columns x3 and x4, the index isn't used at all: DROP TABLE tab; CREATE TABLE tab (x1 TEXT, x2 TEXT, x3, x4, PRIMARY KEY (x1, x3) ) WITHOUT ROWID; CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL; sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL; selectid|order|from|detail 0|0|0|SCAN TABLE tab *** Except if I also remove the "TEXT" type from column x2, the index is used again: DROP TABLE tab; CREATE TABLE tab (x1 TEXT, x2, x3, x4, PRIMARY KEY (x1, x3) ) WITHOUT ROWID; CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL; sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL; selectid|order|from|detail 0|0|0|SCAN TABLE tab USING INDEX ind *** But only until I also remove the "TEXT" type from column x1, then it's not used: DROP TABLE tab; CREATE TABLE tab (x1, x2, x3, x4, PRIMARY KEY (x1, x3) ) WITHOUT ROWID; CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL; sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL; selectid|order|from|detail 0|0|0|SCAN TABLE tab Is this the expected behaviour? Thanks! Martin _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
