On Jan 20, 2008 3:32 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > 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);
Some new info. Exactly this query work very-very slowly: SELECT DISTINCT town_id FROM population_stamp; 3.5.4 explain: addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- 0 OpenEphemeral 1 2 keyinfo(1,BINARY) 1 MemInt 0 2 2 MemInt 0 1 3 Goto 0 13 4 MemInt 1 2 5 Return 0 0 6 IfMemPos 1 8 7 Return 0 0 8 MemLoad 0 0 9 Callback 1 0 10 Return 0 0 11 MemNull 0 0 12 Return 0 0 13 Gosub 0 11 14 Goto 0 41 15 Integer 0 0 16 OpenRead 0 18 17 SetNumColumns 0 2 18 Rewind 0 25 19 StackDepth -1 0 20 Column 0 1 21 Sequence 1 0 22 MakeRecord 2 0 23 IdxInsert 1 0 24 Next 0 19 25 Close 0 0 26 Sort 1 40 27 Column 1 0 28 MemStore 4 0 29 MemLoad 3 0 30 Eq 512 35 collseq(BINARY) 31 MemMove 3 4 32 Gosub 0 6 33 IfMemPos 2 40 34 Gosub 0 11 35 Column 1 0 36 MemStore 0 1 37 MemInt 1 1 38 Next 1 27 39 Gosub 0 6 40 Halt 0 0 41 Transaction 0 0 42 VerifyCookie 0 1619 43 TableLock 0 18 population_stamp 44 Goto 0 15 45 Noop 0 0 3.3.17 explain: explain SELECT DISTINCT town_id FROM population_stamp; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- 0 OpenEphemeral 1 0 keyinfo(1,BINARY) 1 Goto 0 16 2 Integer 0 0 3 OpenRead 0 18 4 SetNumColumns 0 2 5 Rewind 0 14 6 Column 0 1 7 MakeRecord -1 0 8 Distinct 1 11 9 Pop 2 0 10 Goto 0 13 11 IdxInsert 1 0 12 Callback 1 0 13 Next 0 6 14 Close 0 0 15 Halt 0 0 16 Transaction 0 0 17 VerifyCookie 0 1619 18 Goto 0 2 19 Noop 0 0 -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
