Another query using the wrong plan, even though I’ve added a unary “+” to
prevent use of indexes on ‘current’:
SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs
JOIN docs ON docs.doc_id = revs.doc_id
WHERE sequence > ? AND +current=1
ORDER BY revs.doc_id, deleted, revid DESC
0 0 0 SCAN TABLE revs USING COVERING INDEX revs_current
0 1 1 SEARCH TABLE docs USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
Where the revs_current index is
CREATE INDEX revs_current ON revs(doc_id, current desc, deleted, revid
desc);
I’ve tried a number of things and I can’t get the optimizer to stop brute-force
scanning “revs” instead of using the primary key “sequence”.
—Jens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
