The syntactic sugar of the ON clause does nothing for equijoins. Only for outer joins. Please RTFM:
See https://sqlite.org/queryplanner.html And https://sqlite.org/optoverview.html And https://sqlite.org/queryplanner-ng.html -- ˙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 Simon Slavin > Sent: Friday, 28 April, 2017 15:00 > To: SQLite mailing list > Subject: Re: [sqlite] Query plan gone haywire lays waste to my library's > performance > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
