You can always list the tables in the order you want them visited replacing the "," operator with CROSS JOIN to force the optimizer to piss off.
SELECT sequence, revs.doc_id, docid, revid, deleted FROM docs CROSS JOIN revs WHERE sequence > ? AND current=1 AND revs.doc_id = docs.doc_id ORDER BY revs.doc_id, deleted, revid DESC which will FORCE the docs table to be used in the outer loop and revs in the inner loop. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jens Alfke > Sent: Friday, 28 April, 2017 15:31 > To: SQLite mailing list > Subject: [sqlite] Unary + isn't disabling use of index > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users