On 7 Nov 2017, at 7:59am, Davor Josipovic <dav...@live.com> wrote: > What sqlite does now is for each "a" it searches through the index for "x".
If an ideal index already exists, accessing the correct records will be fast. If one does not exist, how would you expect a merge join to be any faster ? There are specific cases where a merge join is faster than using JOIN … ORDER BY. For that to happen, both source tables must already have indexes ideally suited to the merge join, and the rows which you’re going to want returned must be a very large proportion of both source tables, probably the whole tables. Also, SQLite has to be aware of those facts, it cannot simply assume them. Except for the above cases, existing formats will be just as fast, and can be far faster, especially in cases where the rows wanted do not represent most of the rows of the existing tables. Merge joins also represent a problem where you have to compare the two available rows. There’s no good way to know what the programmer means by this, especially in cases involving many columns and collation methods. Assumptions have to be made and whatever the development team picks is sure to annoy some users. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users