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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
