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
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
sqlite-users mailing list