> On 11/17/16, Marc L. Allen 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.
Now I see... Thanks for making it clear.
>
> 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....
Replacing JOIN does not help either:
sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = foo.id
ORDER BY id DESC LIMIT 0, 40;
selectid order from detail
---------- ---------- ----------
----------------------------------------------------------------------------------------------------
0 0 1 SCAN TABLE foo
0 1 0 SEARCH TABLE bar USING INTEGER PRIMARY KEY
(rowid=?)
My guess is it's because there are no sqlite_stat* tables and SQLite doesn't
know that bar scan is more efficient.
That's why there was a LEFT JOIN in the first place, but as it seems, it wasn't
that good idea.
> --
> 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
