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:[email protected]]
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
