Quick workaround: put a "+" on front of the first term of your ORDER BY
clause.
On Thursday, March 22, 2018, 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)
>
> Thanks,
> Jonathan
>
> SELECT
> u.url_id, u.url, l.error_code
> FROM
> urls u
> JOIN
> lookups l
> USING(url_id)
> JOIN (
> SELECT
> url_id,
> MAX(retrieval_datetime) AS retrieval_datetime
> FROM lookups
> WHERE
> url_id IN (
> SELECT url_id FROM urls WHERE url = 'example.com'
> )
> ) recent
>
> ON u.source_seed_id = recent.url_id
> OR u.url_id = recent.url_id
> WHERE
> l.is_generic_flag = 1
> AND
> l.retrieval_datetime >= recent.retrieval_datetime
> AND
> DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 days',
'start of day')
>
>
> --------------------
> Fast EXPLAIN:
>
> 0 Init 0 63 0 00
> 1 Integer 35 1 0 00
> 2 Once 0 35 0 00
> 3 OpenEphemeral 2 2 0 00
> 4 Null 0 2 4 00
> 5 OpenRead 5 50315 0 k(4,-,,,) 02
> 6 Once 0 16 0 00
> 7 OpenEphemeral 7 1 0 k(1,B) 00
> 8 OpenRead 8 8 0 k(2,,) 02
> 9 String8 0 6 0 example.com 00
> 10 SeekGE 8 15 6 1 00
> 11 IdxGT 8 15 6 1 00
> 12 IdxRowid 8 7 0 00
> 13 MakeRecord 7 1 8 C 00
> 14 IdxInsert 7 8 0 00
> 15 Close 8 0 0 00
> 16 Rewind 7 28 0 00
> 17 Column 7 0 5 00
> 18 IsNull 5 27 0 00
> 19 SeekLE 5 27 5 1 00
> 20 IdxLT 5 27 5 1 00
> 21 Column 5 1 8 00
> 22 CollSeq 9 0 0 (BINARY) 00
> 23 AggStep0 0 8 3 max(1) 01
> 24 If 9 26 0 00
> 25 Column 5 0 2 00
> 26 Prev 5 20 0 00
> 27 NextIfOpen 7 17 0 00
> 28 Close 5 0 0 00
> 29 AggFinal 3 1 0 max(1) 00
> 30 SCopy 2 10 0 00
> 31 SCopy 3 11 0 00
> 32 MakeRecord 10 2 8 00
> 33 NewRowid 2 12 0 00
> 34 Insert 2 8 12 08
> 35 Return 1 0 0 00
> 36 OpenRead 1 9 0 8 00
> 37 OpenRead 0 7 0 5 00
> 38 Rewind 2 60 0 00
> 39 Column 2 1 14 00
> 40 Function0 0 14 13 datetime(-1) 01
> 41 Le 15 59 13 51
> 42 Rewind 1 59 0 00
> 43 Column 1 7 13 00
> 44 Ne 16 58 13 (BINARY) 53
> 45 Column 1 3 14 00
> 46 Column 2 1 17 00
> 47 Lt 17 58 14 (BINARY) 53
> 48 Column 1 1 18 00
> 49 SeekRowid 0 58 18 00
> 50 Column 0 4 19 00
> 51 Column 2 0 20 00
> 52 Eq 20 54 19 (BINARY) 43
> 53 Ne 20 58 18 (BINARY) 53
> 54 Copy 18 22 0 00
> 55 Column 0 1 23 00
> 56 Column 1 6 24 00
> 57 ResultRow 22 3 0 00
> 58 Next 1 43 0 01
> 59 Next 2 39 0 01
> 60 Close 1 0 0 00
> 61 Close 0 0 0 00
> 62 Halt 0 0 0 00
> 63 Transaction 0 0 496 0 01
> 64 TableLock 0 9 0 lookups 00
> 65 TableLock 0 7 0 urls 00
> 66 String8 0 25 0 now 00
> 67 String8 0 26 0 -14 days 00
> 68 String8 0 27 0 start of day 00
> 69 Function0 7 25 15 datetime(-1) 03
> 70 Integer 1 16 0 00
> 71 Goto 0 1 0 00
>
>
> -------
> ORDER BY and LIMIT Explain:
> 0 Init 0 77 0 00
> 1 Integer 35 1 0 00
> 2 Once 0 35 0 00
> 3 OpenEphemeral 2 2 0 00
> 4 Null 0 2 4 00
> 5 OpenRead 5 50315 0 k(4,-,,,) 02
> 6 Once 0 16 0 00
> 7 OpenEphemeral 7 1 0 k(1,B) 00
> 8 OpenRead 8 8 0 k(2,,) 02
> 9 String8 0 6 0 example.com 00
> 10 SeekGE 8 15 6 1 00
> 11 IdxGT 8 15 6 1 00
> 12 IdxRowid 8 7 0 00
> 13 MakeRecord 7 1 8 C 00
> 14 IdxInsert 7 8 0 00
> 15 Close 8 0 0 00
> 16 Rewind 7 28 0 00
> 17 Column 7 0 5 00
> 18 IsNull 5 27 0 00
> 19 SeekLE 5 27 5 1 00
> 20 IdxLT 5 27 5 1 00
> 21 Column 5 1 8 00
> 22 CollSeq 9 0 0 (BINARY) 00
> 23 AggStep0 0 8 3 max(1) 01
> 24 If 9 26 0 00
> 25 Column 5 0 2 00
> 26 Prev 5 20 0 00
> 27 NextIfOpen 7 17 0 00
> 28 Close 5 0 0 00
> 29 AggFinal 3 1 0 max(1) 00
> 30 SCopy 2 10 0 00
> 31 SCopy 3 11 0 00
> 32 MakeRecord 10 2 8 00
> 33 NewRowid 2 12 0 00
> 34 Insert 2 8 12 08
> 35 Return 1 0 0 00
> 36 OpenEphemeral 9 5 0 k(1,B) 00
> 37 Integer 1 13 0 00
> 38 OpenRead 1 9 0 8 00
> 39 OpenRead 0 7 0 5 00
> 40 Rewind 2 68 0 00
> 41 Column 2 1 15 00
> 42 Function0 0 15 14 datetime(-1) 01
> 43 Le 16 67 14 51
> 44 Rewind 1 67 0 00
> 45 Column 1 7 14 00
> 46 Ne 17 66 14 (BINARY) 53
> 47 Column 1 3 15 00
> 48 Column 2 1 18 00
> 49 Lt 18 66 15 (BINARY) 53
> 50 Column 1 1 19 00
> 51 SeekRowid 0 66 19 00
> 52 Column 0 4 20 00
> 53 Column 2 0 21 00
> 54 Eq 21 56 20 (BINARY) 43
> 55 Ne 21 66 19 (BINARY) 53
> 56 Copy 19 25 0 00
> 57 Column 0 1 26 00
> 58 Column 1 6 27 00
> 59 Copy 25 23 0 00
> 60 Sequence 9 24 0 00
> 61 MakeRecord 23 5 28 00
> 62 IdxInsert 9 28 0 00
> 63 IfNotZero 13 66 1 00
> 64 Last 9 0 0 00
> 65 Delete 9 0 0 00
> 66 Next 1 45 0 01
> 67 Next 2 41 0 01
> 68 Close 1 0 0 00
> 69 Close 0 0 0 00
> 70 Sort 9 76 0 00
> 71 Column 9 2 25 00
> 72 Column 9 3 26 00
> 73 Column 9 4 27 00
> 74 ResultRow 25 3 0 00
> 75 Next 9 71 0 00
> 76 Halt 0 0 0 00
> 77 Transaction 0 0 496 0 01
> 78 TableLock 0 9 0 lookups 00
> 79 TableLock 0 7 0 urls 00
> 80 String8 0 29 0 now 00
> 81 String8 0 30 0 -14 days 00
> 82 String8 0 31 0 start of day 00
> 83 Function0 7 29 16 datetime(-1) 03
> 84 Integer 1 17 0 00
> 85 Goto 0 1 0 00
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
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
