On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote:
> -- Another interesting thing to note: The INTERSECT test produces ORDERED
> -- output, which suggests that an ORDER-BY addition to the query would
> -- favour the INTERSECT method.
Nothing about INTERSECT requires it to produce ordered output.
Nothing about the JOIN case makes it not possible to produce ordered
output by accident.
You'll want to re-measure with an ORDER BY added.
In any case, this is quite interesting. Many uses of JOIN are not
merely to filter results, but to construct joined result rows -- such
uses of JOIN cannot be optimized by using INTERSECT. But for
filter-uses of JOIN... this might be a useful optimization for the
engine to learn.
sqlite-users mailing list