On 28 Apr 2017, at 9:49pm, Jens Alfke <j...@mooseyard.com> wrote:
> SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs, docs
> WHERE sequence>? AND current!=0 AND deleted=0 AND revs.doc_id = docs.doc_id
> ORDER BY revs.doc_id, deleted, revid DESC
> 0 0 0 SCAN TABLE revs USING INDEX revs_by_docid_revid
> 0 1 1 SEARCH TABLE docs USING INTEGER PRIMARY KEY (rowid=?)
>
> Another query gets it wrong too:
>
> SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs, docs WHERE
> sequence > ? AND current=1 AND revs.doc_id = docs.doc_id 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
What indexes do you have on these two tables ? I can’t recommend one without
knowing which columns belong to which tables.
Once you have the indexes you want, run ANALYZE. Or rather, make sure that the
customers’ database files have had ANALYZE run on them while they contain
typical data.
First query rewritten for clarity:
SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs, docs
WHERE sequence>? AND current!=0 AND deleted=0
AND revs.doc_id = docs.doc_id
ORDER BY revs.doc_id, deleted, revid DESC
Should be more like
SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs
JOIN docs ON docs.doc_id = revs.doc_id
WHERE sequence>? AND current!=0 AND deleted=0
ORDER BY revs.doc_id, deleted, revid DESC
Second query should be more like:
SELECT revs.doc_id, sequence, docid, revid, deleted FROM revs
JOIN docs ON revs.doc_id = docs.doc_id
WHERE sequence > ? AND current=1
ORDER BY revs.doc_id, deleted, revid DESC
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
