Hello everyone.
I've discover performance degradation due to update 3.3.17 -> 3.5.4.
This SQL query work very slowly:
DELETE FROM
population_stamp
WHERE
town_id IN (
SELECT DISTINCT town_id FROM population_stamp
EXCEPT
SELECT id FROM town
);
Some info about tables:
CREATE TABLE population_stamp(
population int,
town_id int,
stamp_id int
);
CREATE INDEX popstamp_idx on population_stamp(stamp_id,
town_id);
CREATE TABLE town(
id int PRIMARY KEY,
lochash int,
x int, y int,
name char(32),
player_id int,
population int
);
CREATE INDEX town_xy ON town(x,y);
CREATE INDEX town_uid ON town(player_id);
CREATE INDEX town_name ON town(name);
Explain from 3.5.4:
sqlite> explain
...> DELETE FROM
...> population_stamp
...> WHERE
...> town_id IN (
...> SELECT DISTINCT town_id FROM population_stamp
...> EXCEPT
...> SELECT id FROM town
...> );
addr opcode p1 p2 p3
---- -------------- ---------- ---------- ---------------------------------
0 Goto 0 104
1 Integer 0 0
2 OpenRead 0 18
3 SetNumColumns 0 2
4 Rewind 0 85
5 StackDepth -1 0
6 MemLoad 0 0
7 If 0 72
8 MemInt 1 0
9 OpenEphemeral 4 0 keyinfo(1,BINARY)
10 SetNumColumns 4 1
11 OpenEphemeral 5 1 keyinfo(1,BINARY)
12 OpenEphemeral 6 2 keyinfo(1,BINARY)
13 MemInt 0 3
14 MemInt 0 2
15 Goto 0 26
16 MemInt 1 3
17 Return 0 0
18 IfMemPos 2 20
19 Return 0 0
20 MemLoad 1 0
21 MakeRecord 1 0
22 IdxInsert 5 0
23 Return 0 0
24 MemNull 1 0
25 Return 0 0
26 Gosub 0 24
27 Integer 0 0
28 OpenRead 2 18
29 SetNumColumns 2 2
30 Rewind 2 37
31 StackDepth -1 0
32 Column 2 1
33 Sequence 6 0
34 MakeRecord 2 0
35 IdxInsert 6 0
36 Next 2 31
37 Close 2 0
38 Sort 6 52
39 Column 6 0
40 MemStore 5 0
41 MemLoad 4 0
42 Eq 512 47 collseq(BINARY)
43 MemMove 4 5
44 Gosub 0 18
45 IfMemPos 3 52
46 Gosub 0 24
47 Column 6 0
48 MemStore 1 1
49 MemInt 1 2
50 Next 6 39
51 Gosub 0 18
52 Integer 0 0
53 OpenRead 1 8
54 SetNumColumns 1 1
55 Rewind 1 62
56 StackDepth -1 0
57 Column 1 0
58 MakeRecord 1 0
59 NotFound 5 61
60 Delete 5 0
61 Next 1 56
62 Close 1 0
63 Rewind 5 71
64 Column 5 0
65 NotNull -1 68
66 Pop 1 0
67 Goto 0 70
68 MakeRecord 1 0 c
69 IdxInsert 4 0
70 Next 5 64
71 Close 5 0
72 Integer 1 0
73 Column 0 1
74 NotNull -1 78
75 Pop 2 0
76 Null 0 0
77 Goto 0 81
78 MakeRecord 1 0 c
79 Found 4 81
80 AddImm -1 0
81 IfNot 1 84
82 Rowid 0 0
83 FifoWrite 0 0
84 Next 0 5
85 Close 0 0
86 Integer 0 0
87 OpenWrite 0 18
88 SetNumColumns 0 3
89 Integer 0 0
90 OpenWrite 1 19 keyinfo(2,BINARY,BINARY)
91 FifoRead 0 101
92 StackDepth -1 0
93 NotExists 0 100
94 Rowid 0 0
95 Column 0 2
96 Column 0 1
97 MakeIdxRec 2 0 dd
98 IdxDelete 1 0
99 Delete 0 1 population_stamp
100 Goto 0 91
101 Close 1 19
102 Close 0 0
103 Halt 0 0
104 Transaction 0 1
105 VerifyCookie 0 151
106 TableLock -1 18 population_stamp
107 TableLock 0 8 town
108 Goto 0 1
109 Noop 0 0
Explain from 3.3.17
0|Goto|0|76|
1|Integer|0|0|
2|OpenRead|0|18|
3|SetNumColumns|0|2|
4|Rewind|0|58|
5|MemLoad|0|0|
6|If|0|45|
7|MemInt|1|0|
8|OpenEphemeral|3|0|keyinfo(1,BINARY)
9|SetNumColumns|3|1|
10|OpenEphemeral|4|1|keyinfo(1,BINARY)
11|OpenEphemeral|5|0|keyinfo(1,BINARY)
12|Integer|0|0|
13|OpenRead|2|18|
14|SetNumColumns|2|2|
15|Rewind|2|25|
16|Column|2|1|
17|MakeRecord|-1|0|
18|Distinct|5|21|
19|Pop|2|0|
20|Goto|0|24|
21|IdxInsert|5|0|
22|MakeRecord|1|0|
23|IdxInsert|4|0|
24|Next|2|16|
25|Close|2|0|
26|Integer|0|0|
27|OpenRead|1|8|
28|SetNumColumns|1|1|
29|Rewind|1|35|
30|Column|1|0|
31|MakeRecord|1|0|
32|NotFound|4|34|
33|Delete|4|0|
34|Next|1|30|
35|Close|1|0|
36|Rewind|4|44|
37|Column|4|0|
38|NotNull|-1|41|
39|Pop|1|0|
40|Goto|0|43|
41|MakeRecord|1|0|c
42|IdxInsert|3|0|
43|Next|4|37|
44|Close|4|0|
45|Integer|1|0|
46|Column|0|1|
47|NotNull|-1|51|
48|Pop|2|0|
49|Null|0|0|
50|Goto|0|54|
51|MakeRecord|1|0|c
52|Found|3|54|
53|AddImm|-1|0|
54|IfNot|1|57|
55|Rowid|0|0|
56|FifoWrite|0|0|
57|Next|0|5|
58|Close|0|0|
59|Integer|0|0|
60|OpenWrite|0|18|
61|SetNumColumns|0|3|
62|Integer|0|0|
63|OpenWrite|1|19|keyinfo(2,BINARY,BINARY)
64|FifoRead|0|73|
65|NotExists|0|72|
66|Rowid|0|0|
67|Column|0|2|
68|Column|0|1|
69|MakeIdxRec|2|0|dd
70|IdxDelete|1|0|
71|Delete|0|1|population_stamp
72|Goto|0|64|
73|Close|1|19|
74|Close|0|0|
75|Halt|0|0|
76|Transaction|0|1|
77|VerifyCookie|0|281|
78|Goto|0|1|
79|Noop|0|0|
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
