I'm not sure that's a valid trick, as bar.foo can be NULL, in which case the LEFT join still returns it, but an INNER join does not. Unless sqlite infers a NOT NULL on bar.foo?
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > Maybe I'm missing something, but... > > ORDER BY id > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > depends on how NULL factors into an ORDER BY. If NULL comes first, it > has to find enough records where the LEFT join fails. > > Yeah.. I'm probably missing something. No, you are correct. Since the ID column is on the RHS of a LEFT JOIN, it cannot use an index for sorting. The foreign key constraint could, in theory, be used by the query planner to simplify the LEFT JOIN into an ordinary INNER JOIN. But the query planner in SQLite does not currently know that trick. So, one solution is to remove the LEFT keyword from the query in the application.... -- 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
