Why don't you just explicitly sort by bar.foo?
sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; 0|0|0|SCAN TABLE bar 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) sqlite> Dimitris ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Nico Williams <n...@cryptonector.com> Sent: Thursday, November 17, 2016 4:32 PM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On Thu, Nov 17, 2016 at 04:48:20PM +0200, Paul wrote: > 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. If you want the ORDER BY to not sort (either as results are generated or after, but either way before the application sees a single result) then you kinda need to scan foo first. Is there any other way? I don't think so, but correct me! Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users sqlite-users Info Page<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> mailinglists.sqlite.org To see the collection of prior postings to the list, visit the sqlite-users Archives. (The current archive is only available to the list ... _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
