Although it may not translate as well to the more complex examples, would you also consider adding the IN operator to your tests? I found for example that "select v from t1 where v in t2;" did even better than the join or the intersect.
Other "am I right in thinking this" question: INTERSECT is only going to be viable when comparing full records, correct? If you're looking to filter table A by whether its primary key is also a primary key for table B, but ignoring the other fields in both, then INTERSECT becomes not an option, or at least starts making the query more complex/ugly... correct? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, September 07, 2017 8:06 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency On 2017/09/07 3:43 AM, Keith Medcalf wrote: > Try the same test using 147 columns in each table. Exactly the plan for this weekend :) > 1 column is rather trivial. Even a kindergarten kid could do it in no time > using crayons and the wall. So? That is non-sequitur, I am sure given enough crayons, wall-space and time, a kindergarten kid can do it with 147 columns too. That says exactly nothing about the possible efficiencies of different methods. If however the 1-columness of the test gets somehow advantaged by being the PK (as Nico pointed out) or real world data such as TEXT entries sort slower than INTs, then it might affect it, so the 147 column tests will tell. > In other words except in very trivial cases (like having only one column that > is not nullable) it will be very difficult to write a "correct" JOIN or > correlated subquery that emulates an INTERSECT. Well I agree, but it is those trivial cases that are of interest here, and if there is a general JOIN optimization to be had. The INTERSECT test merely served as the catalyst to put us on the trail of the possible JOIN optimization, if there is even an optimization to be had (it might yet be a wild goose chase, which you seem to have your money on, so watch this space, I'll graciously accept your "told ya!" later after testing). Cheers, Ryan _______________________________________________ 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
