On 11/5/17, Davor Josipovic <dav...@live.com> wrote:
> Merge joins could be an incredible optimization in some cases for large
> queries and would make sqlite much faster in such cases.
SQLite does do a merge in some cases, though not for what you would
traditionally call a join. For example, SQLite will do a merge to
combine the two halves of this query:
SELECT a,b,c FROM tab1 UNION SELECT x,y,z FROM tab2 ORDER BY 1,2,3;
You are thinking that perhaps queries such as the following might be
faster using a merge:
SELECT * FROM tab1 JOIN tab2 ON tab1.a=tab2.x;
I disagree. In order to do this as a merge, we'd need indexes on both
tab1.a and tab2.x. (In order for the merge to be practical, and not
require an arbitrary amount of auxiliary storage, both indexes would
need to be UNIQUE.) But if you already either one of those two
indexes, then the nested-loop join will already be blazing fast. It
is difficult to see how switching to a merge join would make it go any
faster.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
