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
