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) -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Poor Yorick Sent: Wednesday, April 17, 2019 6:32 AM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] slow join, fast subselect On Wed, Apr 17, 2019 at 10:15:31AM +0000, Hick Gunter wrote: > Try EXPLAIN QUERY PLAN <stmt> or even EXPLAIN <stmt> to see what is going on > in each case. I already have, of course. The question is, how much effort would it be to get sqlite choose the better query plan in the "join" case as well? -- Poor Yorick _______________________________________________ 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
