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] -----------------------------------------------------------------------------