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
