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:[email protected]] On Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list <[email protected]> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <[email protected]> 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 [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
