On 4/28/2017 5:30 PM, Jens Alfke wrote:
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);
It seems the index is used to implement "ORDER BY revs.doc_id" part. Try "ORDER BY +revs.doc_id"
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
