On Wed, Apr 17, 2019 at 11:36:18AM +0200, R Smith wrote: > On 2019/04/17 10:55 AM, Poor Yorick wrote: [SNIP] > > In your above example you really wish to know all the a's which have an > entry in b. The first query asks to join and list all b's found for every a > (which works mathematically in this case by virtue of rowid uniqueness, but > isn't the real question and forces a lot of "join" algorithm checking on the > QP), the correct question is the second query: Show every a which can also > be found in b. It releases the QP of a lot of responsibility and let's it > follow a plan that is much faster. > > > Hope that makes sense :) > > Ryan >
That's an apt and accessible description of the issue, but at the denotational level the meanings of the queries are in fact identical under the conditions you enumerated. Ideally sqlite would notice and adjust its query plan accordingly. If the cost of doing so doesn't justify the effort, that could be documented. As good as the sqlite documentation is, it currently lacks this sort of higher-level guidance. -- Poor Yorick _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
