Hi Simon,
Sure; I didn't include them because the only difference is the last
line, and that just seems to be the standard "ordering" line. I figured
the explain was more useful as a lot has changed in that.
Cheers,
Jonathan
Fast version:
1 0 0 SEARCH TABLE lookups USING COVERING INDEX sgdsfweer
(url_id=?)
1 0 0 EXECUTE LIST SUBQUERY 2
2 0 0 SEARCH TABLE urls USING COVERING INDEX
sqlite_autoindex_urls_1 (url=?)
0 0 2 SCAN SUBQUERY 1 AS recent
0 1 1 SCAN TABLE lookups AS l
0 2 0 SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)
Slow version (Order by and LIMIT):
1 0 0 SEARCH TABLE lookups USING COVERING INDEX sgdsfweer
(url_id=?)
1 0 0 EXECUTE LIST SUBQUERY 2
2 0 0 SEARCH TABLE urls USING COVERING INDEX
sqlite_autoindex_urls_1 (url=?)
0 0 2 SCAN SUBQUERY 1 AS recent
0 1 1 SCAN TABLE lookups AS l
0 2 0 SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 USE TEMP B-TREE FOR ORDER BY
On 2018-03-22 22:01, Simon Slavin wrote:
On 22 Mar 2018, at 9:24pm, Jonathan Moules <jonathan-li...@lightpear.com> wrote:
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.
Hi, Jonathan.
Could you post details similar to the ones in that post, but instead of using
EXPLAIN use EXPLAIN QUERY PLAN ? This will give results on a scale which is
easier to understand in the context of your queries and indexes.
<https://www.sqlite.org/eqp.html>
Simon.
_______________________________________________
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
