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. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
