On Wed, Apr 17, 2019 at 01:24:11PM +0000, David Raymond wrote: > Would you post what those explain query plans results are? All the other > replies not withstanding I'm still curious as to why #2 would be faster > (assuming "rowid" is indeed the actual rowid anyway) > > Also, is that a typo in #2, if you're not using b, why would you include it > in the from clause? Wouldn't that introduce a whole bunch of duplicates? As > in a copy of a.rowid for every single record in b? (Maybe my brain just > hasn't finished warming up this morning) > > #1 > select a.rowid > from a join b on a.rowid = b.rowid > where a.ref = $x > > #2 > select a.rowid > from a,b > where a.ref = $x and a.rowid in (select rowid from b) > >
3 0 0 {SEARCH TABLE a USING COVERING INDEX idx_ref (ref=?)} 8 0 0 {SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)} 2 0 0 {SEARCH TABLE a USING COVERING INDEX idx_ref (ref=? AND rowid=?)} 7 0 0 {USING ROWID SEARCH ON TABLE b FOR IN-OPERATOR} -- Poor Yorick _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users