On 23 March 2018 at 05:24, Jonathan Moules <jonathan-li...@lightpear.com>
wrote:
> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
> (preview)) despite looking through hundreds of thousands of records in each
> table, and it returns 86 records in all. This is great!
>
> But when I stick an "ORDER BY" on the end (either ASC or DESC), the
> processing time shoots up to 0.15s. The EXPLAIN between the two is
> considerably different so it seems the ORDER BY is getting it to use a
> sub-optimal query plan.
> If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
> plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
> are also here (they start changing at item 36).
>
> Any suggestions for what's going on here and how to coerce the planner to
> stick to the fast-path and then do a simple order by on those 86 (or
> however many - it'll always be a low number) results?
> (ANALYZE has been run)
>
Does it help if you move the ORDER BY to an outer select? ie:
SELECT id, u, err FROM (
SELECT u.url_id, u.url, l.error_code ...
) ORDER BY id;
If the query planner flattens the subquery this probably won't make a
difference though...
-Rowan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
