Yes, there are triggers. I didn't post them because I didn't see any correlation to the DELETE query as they are only INSERT and UPDATE triggers:
CREATE TRIGGER item_inserted AFTER INSERT ON item BEGIN UPDATE trace SET typeid = (SELECT id FROM "type" WHERE name = 'modified') WHERE trace.id = new.traceid; END CREATE TRIGGER item_modified AFTER UPDATE OF traceid, freq, value, noiseflag ON item BEGIN UPDATE trace SET typeid = (SELECT id FROM "type" WHERE name = 'modified') WHERE trace.id IN (old.traceid, new.traceid); END CREATE TRIGGER meta_global_insert INSTEAD OF INSERT ON meta_global FOR EACH ROW BEGIN INSERT INTO metadata (parameter, value) VALUES (NEW.parameter, NEW.value); END CREATE TRIGGER meta_global_update INSTEAD OF UPDATE OF parameter, value ON meta_global BEGIN UPDATE metadata SET parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id AND datasetid IS NULL; END CREATE TRIGGER meta_dataset_insert INSTEAD OF INSERT ON meta_dataset FOR EACH ROW WHEN NEW.datasetid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, datasetid) VALUES (NEW.parameter, NEW.value, NEW.datasetid); END CREATE TRIGGER meta_dataset_update INSTEAD OF UPDATE OF datasetid, parameter, value ON meta_dataset WHEN NEW.datasetid IS NOT NULL BEGIN UPDATE metadata SET datasetid=NEW.datasetid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id AND traceid IS NULL; END CREATE TRIGGER meta_trace_insert INSTEAD OF INSERT ON meta_trace FOR EACH ROW WHEN NEW.traceid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, datasetid, traceid) SELECT NEW.parameter, NEW.value, datasetid, NEW.traceid FROM trace WHERE id=NEW.traceid; END CREATE TRIGGER meta_trace_update INSTEAD OF UPDATE OF traceid, parameter, value ON meta_trace WHEN NEW.traceid IS NOT NULL BEGIN UPDATE metadata SET datasetid=(SELECT datasetid FROM trace WHERE id=NEW.traceid), traceid=NEW.traceid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id AND itemid IS NULL; END CREATE TRIGGER meta_item_insert INSTEAD OF INSERT ON meta_item FOR EACH ROW WHEN NEW.itemid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, datasetid, traceid, traceid) SELECT NEW.parameter, NEW.value, t.datasetid, t.id, NEW.itemid FROM itemid i LEFT JOIN trace t ON i.traceid=t.id WHERE id=NEW.itemid; END CREATE TRIGGER meta_item_update INSTEAD OF UPDATE OF itemid, parameter, value ON meta_item WHEN NEW.itemid IS NOT NULL BEGIN UPDATE metadata SET datasetid=(SELECT t.datasetid FROM item i LEFT JOIN trace t ON i.traceid=t.id WHERE t.id=NEW.itemid), traceid=(SELECT traceid FROM item WHERE id=NEW.itemid), itemid=NEW.itemid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id; END ----- Original Message ----- From: David Raymond <david.raym...@tomtom.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Friday, November 1, 2019, 17:27:27 Subject: [sqlite] DELETE extremely slow Looks like you have triggers going on there. You only gave us the table and index definitions. What are the on delete triggers you have? -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Thomas Kurz Sent: Thursday, October 31, 2019 6:54 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] DELETE extremely slow > Do you have memory to run this in? Have you increased the sqlite cache size > because that looks (to me) an awful lot like I/O thrashing ... Sorry to disappoint you, Keith and Simon, but in all cases the database file has been located on a ramdisk. It's only about 50 MB in size, btw. > SQLite runs on the local machine. While MariaDB is client-server, so the > delete effectively runs on the server. Yes and no. Of curse, I had MariaDB run on the same machine, and its data files had been stored on the same ramdisk. > How much of the 88 minutes is "waiting" time? I did it again, same file: SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> .timer on sqlite> delete from dataset; Run Time: real 5249.891 user 2412.812500 sys 2606.531250 > You haven't shown the "explain query plan" Keith asked for Is the beginning of it enough or do I have to repeat the entire DELETE? Here is the output which I canceled after some seconds: QUERY PLAN |--SCAN TABLE dataset |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SCAN TABLE item |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) `--SCAN TABLE item addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 30 0 00 Start at 30 1 Null 0 1 0 00 r[1]=NULL 2 OpenRead 0 7 0 0 00 root=7 iDb=0; dataset 3 Rewind 0 7 0 00 4 Rowid 0 2 0 00 r[2]=rowid 5 RowSetAdd 1 2 0 00 rowset(1)=r[2] 6 Next 0 4 0 01 7 OpenWrite 0 7 0 3 00 root=7 iDb=0; dataset 8 RowSetRead 1 29 2 00 r[2]=rowset(1) 9 NotExists 0 28 2 1 00 intkey=r[2] 10 Copy 2 3 0 00 r[3]=r[2] 11 OpenRead 2 17 0 k(3,,,) 02 root=17 iDb=0; metadata_idx_04 12 IsNull 3 18 0 00 if r[3]==NULL goto 18 13 Affinity 3 1 0 C 00 affinity(r[3]) 14 SeekGE 2 18 3 1 00 key=r[3] 15 IdxGT 2 18 3 1 00 key=r[3] 16 FkCounter 0 1 0 00 fkctr[0]+=1 17 Next 2 15 0 00 18 OpenRead 4 13869 0 k(2,,) 02 root=13869 iDb=0; trace_idx_03 19 IsNull 3 25 0 00 if r[3]==NULL goto 25 20 Affinity 3 1 0 C 00 affinity(r[3]) 21 SeekGE 4 25 3 1 00 key=r[3] 22 IdxGT 4 25 3 1 00 key=r[3] 23 FkCounter 0 1 0 00 fkctr[0]+=1 24 Next 4 22 1 00 25 Delete 0 1 0 dataset 00 26 Program 3 0 9 program 00 Call: fkey.abort 27 Program 3 0 10 program 00 Call: fkey.abort 28 Goto 0 8 0 00 29 Halt 0 0 0 00 30 Transaction 0 1 40 0 01 usesStmtJournal=0 31 Goto 0 1 0 00 0 Init 0 1 0 00 Start at 1; Start: .abort (AFTER DELETE ON dataset) 1 Null 0 1 0 00 r[1]=NULL 2 OpenRead 11 17 0 k(3,,,) 02 root=17 iDb=0; metadata_idx_04 3 Param 0 2 0 00 r[2]=old.rowid 4 IsNull 2 11 0 00 if r[2]==NULL goto 11 5 Affinity 2 1 0 C 00 affinity(r[2]) 6 SeekGE 11 11 2 1 00 key=r[2] 7 IdxGT 11 11 2 1 00 key=r[2] 8 IdxRowid 11 3 0 00 r[3]=rowid 9 RowSetAdd 1 3 0 00 rowset(1)=r[3] 10 Next 11 7 0 00 11 OpenWrite 0 10 0 6 00 root=10 iDb=0; metadata 12 OpenWrite 1 23 0 k(5,NOCASE,,,,) 08 root=23 iDb=0; metadata_idx_10 13 OpenWrite 2 22 0 k(3,NOCASE,,) 08 root=22 iDb=0; metadata_idx_09 14 OpenWrite 3 21 0 k(3,,NOCASE,) 08 root=21 iDb=0; metadata_idx_08 15 OpenWrite 4 20 0 k(3,,NOCASE,) 08 root=20 iDb=0; metadata_idx_07 16 OpenWrite 5 19 0 k(2,,) 08 root=19 iDb=0; metadata_idx_06 17 OpenWrite 6 18 0 k(3,,,) 08 root=18 iDb=0; metadata_idx_05 18 OpenWrite 7 17 0 k(3,,,) 08 root=17 iDb=0; metadata_idx_04 19 OpenWrite 8 16 0 k(2,,) 08 root=16 iDb=0; metadata_idx_03 20 OpenWrite 9 15 0 k(3,,,) 08 root=15 iDb=0; metadata_idx_02 21 OpenWrite 10 14 0 k(6,NOCASE,NOCASE,,,,) 08 root=14 iDb=0; metadata_idx_01 22 RowSetRead 1 101 3 00 r[3]=rowset(1) 23 NotExists 0 100 3 1 00 intkey=r[3] 24 Copy 3 4 0 00 r[4]=r[3] 25 Column 0 3 8 NULL 00 r[8]=metadata.datasetid 26 Column 0 4 9 NULL 00 r[9]=metadata.traceid 27 Column 0 5 10 NULL 00 r[10]=metadata.itemid 28 FkIfZero 0 36 0 00 if fkctr[0]==0 goto 36 29 IsNull 10 36 0 00 if r[10]==NULL goto 36 30 SCopy 10 11 0 00 r[11]=r[10] 31 MustBeInt 11 35 0 00 32 OpenRead 12 9 0 6 00 root=9 iDb=0; item 33 NotExists 12 35 11 00 intkey=r[11] 34 Goto 0 36 0 00 35 FkCounter 0 -1 0 00 fkctr[0]+=-1 36 Close 12 0 0 00 37 FkIfZero 0 45 0 00 if fkctr[0]==0 goto 45 38 IsNull 9 45 0 00 if r[9]==NULL goto 45 39 SCopy 9 11 0 00 r[11]=r[9] 40 MustBeInt 11 44 0 00 41 OpenRead 13 8 0 7 00 root=8 iDb=0; trace 42 NotExists 13 44 11 00 intkey=r[11] 43 Goto 0 45 0 00 44 FkCounter 0 -1 0 00 fkctr[0]+=-1 45 Close 13 0 0 00 46 FkIfZero 0 54 0 00 if fkctr[0]==0 goto 54 47 IsNull 8 54 0 00 if r[8]==NULL goto 54 48 SCopy 8 11 0 00 r[11]=r[8] 49 MustBeInt 11 53 0 00 50 OpenRead 14 7 0 3 00 root=7 iDb=0; dataset 51 NotExists 14 53 11 00 intkey=r[11] 52 Goto 0 54 0 00 53 FkCounter 0 -1 0 00 fkctr[0]+=-1 54 Close 14 0 0 00 55 OpenRead 15 9 0 6 00 root=9 iDb=0; item 56 Rewind 15 61 0 00 57 Column 15 5 11 00 r[11]=item.nameid 58 Ne 4 60 11 (BINARY) 53 if r[11]!=r[4] goto 60 59 FkCounter 0 1 0 00 fkctr[0]+=1 60 Next 15 57 0 01 61 Column 0 1 13 00 r[13]=metadata.parameter 62 Column 0 3 14 NULL 00 r[14]=metadata.datasetid 63 Column 0 4 15 NULL 00 r[15]=metadata.traceid 64 Column 0 5 16 NULL 00 r[16]=metadata.itemid 65 Rowid 0 17 0 00 r[17]=rowid 66 IdxDelete 1 13 5 00 key=r[13..17] 67 Column 0 4 14 NULL 00 r[14]=metadata.traceid 68 Rowid 0 15 0 00 r[15]=rowid 69 IdxDelete 2 13 3 00 key=r[13..15] 70 Column 0 4 13 NULL 00 r[13]=metadata.traceid 71 Column 0 1 14 00 r[14]=metadata.parameter 72 IdxDelete 3 13 3 00 key=r[13..15] 73 Column 0 3 13 NULL 00 r[13]=metadata.datasetid 74 IdxDelete 4 13 3 00 key=r[13..15] 75 Column 0 5 13 NULL 00 r[13]=metadata.itemid 76 Rowid 0 14 0 00 r[14]=rowid 77 IdxDelete 5 13 2 00 key=r[13..14] 78 Column 0 4 18 NULL 00 r[18]=metadata.traceid 79 Column 0 5 19 NULL 00 r[19]=metadata.itemid 80 Rowid 0 20 0 00 r[20]=rowid 81 IdxDelete 6 18 3 00 key=r[18..20] 82 Column 0 3 18 NULL 00 r[18]=metadata.datasetid 83 IdxDelete 7 18 3 00 key=r[18..20] 84 Column 0 4 18 NULL 00 r[18]=metadata.traceid 85 Rowid 0 19 0 00 r[19]=rowid 86 IdxDelete 8 18 2 00 key=r[18..19] 87 Column 0 3 21 NULL 00 r[21]=metadata.datasetid 88 Column 0 4 22 NULL 00 r[22]=metadata.traceid 89 Rowid 0 23 0 00 r[23]=rowid 90 IdxDelete 9 21 3 00 key=r[21..23] 91 Column 0 1 24 00 r[24]=metadata.parameter 92 Column 0 2 25 00 r[25]=metadata.value 93 Column 0 3 26 NULL 00 r[26]=metadata.datasetid 94 Column 0 4 27 NULL 00 r[27]=metadata.traceid 95 Column 0 5 28 NULL 00 r[28]=metadata.itemid 96 Rowid 0 29 0 00 r[29]=rowid 97 IdxDelete 10 24 6 00 key=r[24..29] 98 Delete 0 1 0 metadata 00 99 Program 4 0 30 program 00 Call: fkey.abort 100 Goto 0 22 0 00 101 ResetCount 0 0 0 00 102 Halt 0 0 0 00 End: .abort 0 Init 0 1 0 00 Start at 1; Start: .abort (AFTER DELETE ON dataset) 1 Null 0 1 0 00 r[1]=NULL 2 OpenRead 4 13869 0 k(2,,) 02 root=13869 iDb=0; trace_idx_03 3 Param 0 2 0 00 r[2]=old.rowid 4 IsNull 2 11 0 00 if r[2]==NULL goto 11 5 Affinity 2 1 0 C 00 affinity(r[2]) 6 SeekGE 4 11 2 1 00 key=r[2] 7 IdxGT 4 11 2 1 00 key=r[2] 8 IdxRowid 4 3 0 00 r[3]=rowid 9 RowSetAdd 1 3 0 00 rowset(1)=r[3] 10 Next 4 7 1 00 11 OpenWrite 0 8 0 7 00 root=8 iDb=0; trace 12 OpenWrite 1 13869 0 k(2,,) 08 root=13869 iDb=0; trace_idx_03 13 OpenWrite 2 25 0 k(2,,) 08 root=25 iDb=0; trace_idx_02 14 OpenWrite 3 24 0 k(2,,) 08 root=24 iDb=0; trace_idx_01 15 RowSetRead 1 73 3 00 r[3]=rowset(1) 16 NotExists 0 72 3 1 00 intkey=r[3] 17 Copy 3 4 0 00 r[4]=r[3] 18 Column 0 2 7 NULL 00 r[7]=trace.typeid 19 Column 0 3 8 00 r[8]=trace.datasetid 20 Column 0 4 9 00 r[9]=trace.quantityid 21 FkIfZero 0 29 0 00 if fkctr[0]==0 goto 29 22 IsNull 9 29 0 00 if r[9]==NULL goto 29 23 SCopy 9 12 0 00 r[12]=r[9] 24 MustBeInt 12 28 0 00 25 OpenRead 5 2 0 5 00 root=2 iDb=0; quantity 26 NotExists 5 28 12 00 intkey=r[12] 27 Goto 0 29 0 00 28 FkCounter 0 -1 0 00 fkctr[0]+=-1 29 Close 5 0 0 00 30 FkIfZero 0 38 0 00 if fkctr[0]==0 goto 38 31 IsNull 8 38 0 00 if r[8]==NULL goto 38 32 SCopy 8 12 0 00 r[12]=r[8] 33 MustBeInt 12 37 0 00 34 OpenRead 6 7 0 3 00 root=7 iDb=0; dataset 35 NotExists 6 37 12 00 intkey=r[12] 36 Goto 0 38 0 00 37 FkCounter 0 -1 0 00 fkctr[0]+=-1 38 Close 6 0 0 00 39 FkIfZero 0 47 0 00 if fkctr[0]==0 goto 47 40 IsNull 7 47 0 00 if r[7]==NULL goto 47 41 SCopy 7 12 0 00 r[12]=r[7] 42 MustBeInt 12 46 0 00 43 OpenRead 7 5 0 3 00 root=5 iDb=0; type 44 NotExists 7 46 12 00 intkey=r[12] 45 Goto 0 47 0 00 46 FkCounter 0 -1 0 00 fkctr[0]+=-1 47 Close 7 0 0 00 48 OpenRead 9 16 0 k(2,,) 02 root=16 iDb=0; metadata_idx_03 49 IsNull 4 55 0 00 if r[4]==NULL goto 55 50 Affinity 4 1 0 C 00 affinity(r[4]) 51 SeekGE 9 55 4 1 00 key=r[4] 52 IdxGT 9 55 4 1 00 key=r[4] 53 FkCounter 0 1 0 00 fkctr[0]+=1 54 Next 9 52 1 00 55 OpenRead 11 27 0 k(2,,) 02 root=27 iDb=0; item_idx_01 56 IsNull 4 62 0 00 if r[4]==NULL goto 62 57 Affinity 4 1 0 C 00 affinity(r[4]) 58 SeekGE 11 62 4 1 00 key=r[4] 59 IdxGT 11 62 4 1 00 key=r[4] 60 FkCounter 0 1 0 00 fkctr[0]+=1 61 Next 11 59 1 00 62 Column 0 3 15 00 r[15]=trace.datasetid 63 Rowid 0 16 0 00 r[16]=rowid 64 IdxDelete 1 15 2 00 key=r[15..16] 65 Rowid 0 15 0 00 r[15]=rowid; trace.id 66 IdxDelete 2 15 2 00 key=r[15..16] 67 Column 0 4 15 00 r[15]=trace.quantityid 68 IdxDelete 3 15 2 00 key=r[15..16] 69 Delete 0 1 0 trace 00 70 Program 4 0 17 program 00 Call: fkey.abort 71 Program 4 0 18 program 00 Call: fkey.abort 72 Goto 0 15 0 00 73 ResetCount 0 0 0 00 74 Halt 0 0 0 00 End: .abort 0 Init 0 1 0 00 Start at 1; Start: .abort (AFTER DELETE ON metadata) 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenRead 0 9 0 6 00 root=9 iDb=0; item 3 Rewind 0 10 0 00 4 Column 0 5 16 00 r[16]=item.nameid 5 Param 0 17 0 00 r[17]=old.rowid 6 Ne 17 9 16 (BINARY) 53 if r[16]!=r[17] goto 9 7 Rowid 0 2 0 00 r[2]=rowid 8 RowSetAdd 1 2 0 00 rowset(1)=r[2] 9 Next 0 4 0 01 10 OpenWrite 0 9 0 6 00 root=9 iDb=0; item 11 RowSetRead 1 39 2 00 r[2]=rowset(1) 12 NotExists 0 11 2 00 intkey=r[2] 13 Rowid 0 3 0 00 r[3]=rowid; item.id 14 Column 0 1 4 00 r[4]=item.traceid 15 Null 0 5 0 00 r[5]=NULL 16 Null 0 6 0 00 r[6]=NULL 17 Null 0 7 0 00 r[7]=NULL 18 Column 0 5 8 00 r[8]=item.nameid 19 Copy 2 9 0 00 r[9]=r[2] 20 Null 0 10 0 00 r[10]=NULL 21 Column 0 1 11 00 r[11]=item.traceid 22 Column 0 2 12 00 r[12]=item.freq 23 Column 0 3 13 00 r[13]=item.value 24 RealAffinity 13 0 0 00 25 Column 0 4 14 NULL 00 r[14]=item.noiseflag 26 Null 0 15 0 00 r[15]=NULL 27 MakeRecord 10 6 18 DDDECD 00 r[18]=mkrec(r[10..15]) 28 FkIfZero 0 36 0 00 if fkctr[0]==0 goto 36 29 IsNull 8 36 0 00 if r[8]==NULL goto 36 30 SCopy 8 17 0 00 r[17]=r[8] 31 MustBeInt 17 35 0 00 32 OpenRead 2 10 0 6 00 root=10 iDb=0; metadata 33 NotExists 2 35 17 00 intkey=r[17] 34 Goto 0 36 0 00 35 FkCounter 0 -1 0 00 fkctr[0]+=-1 36 Close 2 0 0 00 37 Insert 0 18 9 item 05 intkey=r[9] data=r[18] 38 Goto 0 11 0 00 39 ResetCount 0 0 0 00 40 Halt 0 0 0 00 End: .abort 0 Init 0 1 0 00 Start at 1; Start: .abort (AFTER DELETE ON trace) 1 Null 0 1 0 00 r[1]=NULL 2 OpenRead 11 16 0 k(2,,) 02 root=16 iDb=0; metadata_idx_03 3 Param 0 2 0 00 r[2]=old.rowid 4 IsNull 2 11 0 00 if r[2]==NULL goto 11 5 Affinity 2 1 0 C 00 affinity(r[2]) 6 SeekGE 11 11 2 1 00 key=r[2] 7 IdxGT 11 11 2 1 00 key=r[2] 8 IdxRowid 11 3 0 00 r[3]=rowid 9 RowSetAdd 1 3 0 00 rowset(1)=r[3] 10 Next 11 7 1 00 11 OpenWrite 0 10 0 6 00 root=10 iDb=0; metadata 12 OpenWrite 1 23 0 k(5,NOCASE,,,,) 08 root=23 iDb=0; metadata_idx_10 13 OpenWrite 2 22 0 k(3,NOCASE,,) 08 root=22 iDb=0; metadata_idx_09 14 OpenWrite 3 21 0 k(3,,NOCASE,) 08 root=21 iDb=0; metadata_idx_08 15 OpenWrite 4 20 0 k(3,,NOCASE,) 08 root=20 iDb=0; metadata_idx_07 16 OpenWrite 5 19 0 k(2,,) 08 root=19 iDb=0; metadata_idx_06 17 OpenWrite 6 18 0 k(3,,,) 08 root=18 iDb=0; metadata_idx_05 18 OpenWrite 7 17 0 k(3,,,) 08 root=17 iDb=0; metadata_idx_04 19 OpenWrite 8 16 0 k(2,,) 08 root=16 iDb=0; metadata_idx_03 20 OpenWrite 9 15 0 k(3,,,) 08 root=15 iDb=0; metadata_idx_02 21 OpenWrite 10 14 0 k(6,NOCASE,NOCASE,,,,) 08 root=14 iDb=0; metadata_idx_01 22 RowSetRead 1 101 3 00 r[3]=rowset(1) 23 NotExists 0 100 3 1 00 intkey=r[3] 24 Copy 3 4 0 00 r[4]=r[3] 25 Column 0 3 8 NULL 00 r[8]=metadata.datasetid 26 Column 0 4 9 NULL 00 r[9]=metadata.traceid 27 Column 0 5 10 NULL 00 r[10]=metadata.itemid 28 FkIfZero 0 36 0 00 if fkctr[0]==0 goto 36 29 IsNull 10 36 0 00 if r[10]==NULL goto 36 30 SCopy 10 11 0 00 r[11]=r[10] 31 MustBeInt 11 35 0 00 32 OpenRead 12 9 0 6 00 root=9 iDb=0; item 33 NotExists 12 35 11 00 intkey=r[11] 34 Goto 0 36 0 00 35 FkCounter 0 -1 0 00 fkctr[0]+=-1 36 Close 12 0 0 00 37 FkIfZero 0 45 0 00 if fkctr[0]==0 goto 45 38 IsNull 9 45 0 00 if r[9]==NULL goto 45 39 SCopy 9 11 0 00 r[11]=r[9] 40 MustBeInt 11 44 0 00 41 OpenRead 13 8 0 7 00 root=8 iDb=0; trace 42 NotExists 13 44 11 00 intkey=r[11] 43 Goto 0 45 0 00 44 FkCounter 0 -1 0 00 fkctr[0]+=-1 45 Close 13 0 0 00 46 FkIfZero 0 54 0 00 if fkctr[0]==0 goto 54 47 IsNull 8 54 0 00 if r[8]==NULL goto 54 48 SCopy 8 11 0 00 r[11]=r[8] 49 MustBeInt 11 53 0 00 50 OpenRead 14 7 0 3 00 root=7 iDb=0; dataset 51 NotExists 14 53 11 00 intkey=r[11] 52 Goto 0 54 0 00 53 FkCounter 0 -1 0 00 fkctr[0]+=-1 54 Close 14 0 0 00 55 OpenRead 15 9 0 6 00 root=9 iDb=0; item 56 Rewind 15 61 0 00 57 Column 15 5 11 00 r[11]=item.nameid 58 Ne 4 60 11 (BINARY) 53 if r[11]!=r[4] goto 60 59 FkCounter 0 1 0 00 fkctr[0]+=1 60 Next 15 57 0 01 61 Column 0 1 13 00 r[13]=metadata.parameter 62 Column 0 3 14 NULL 00 r[14]=metadata.datasetid 63 Column 0 4 15 NULL 00 r[15]=metadata.traceid 64 Column 0 5 16 NULL 00 r[16]=metadata.itemid 65 Rowid 0 17 0 00 r[17]=rowid 66 IdxDelete 1 13 5 00 key=r[13..17] 67 Column 0 4 14 NULL 00 r[14]=metadata.traceid 68 Rowid 0 15 0 00 r[15]=rowid 69 IdxDelete 2 13 3 00 key=r[13..15] 70 Column 0 4 13 NULL 00 r[13]=metadata.traceid 71 Column 0 1 14 00 r[14]=metadata.parameter 72 IdxDelete 3 13 3 00 key=r[13..15] 73 Column 0 3 13 NULL 00 r[13]=metadata.datasetid 74 IdxDelete 4 13 3 00 key=r[13..15] 75 Column 0 5 13 NULL 00 r[13]=metadata.itemid 76 Rowid 0 14 0 00 r[14]=rowid 77 IdxDelete 5 13 2 00 key=r[13..14] 78 Column 0 4 18 NULL 00 r[18]=metadata.traceid 79 Column 0 5 19 NULL 00 r[19]=metadata.itemid 80 Rowid 0 20 0 00 r[20]=rowid 81 IdxDelete 6 18 3 00 key=r[18..20] 82 Column 0 3 18 NULL 00 r[18]=metadata.datasetid 83 IdxDelete 7 18 3 00 key=r[18..20] 84 Column 0 4 18 NULL 00 r[18]=metadata.traceid 85 Rowid 0 19 0 00 r[19]=rowid 86 IdxDelete 8 18 2 00 key=r[18..19] 87 Column 0 3 21 NULL 00 r[21]=metadata.datasetid 88 Column 0 4 22 NULL 00 r[22]=metadata.traceid 89 Rowid 0 23 0 00 r[23]=rowid 90 IdxDelete 9 21 3 00 key=r[21..23] 91 Column 0 1 24 00 r[24]=metadata.parameter 92 Column 0 2 25 00 r[25]=metadata.value 93 Column 0 3 26 NULL 00 r[26]=metadata.datasetid 94 Column 0 4 27 NULL 00 r[27]=metadata.traceid 95 Column 0 5 28 NULL 00 r[28]=metadata.itemid 96 Rowid 0 29 0 00 r[29]=rowid 97 IdxDelete 10 24 6 00 key=r[24..29] 98 Delete 0 1 0 metadata 00 99 Program 4 0 30 program 00 Call: fkey.abort 100 Goto 0 22 0 00 101 ResetCount 0 0 0 00 102 Halt 0 0 0 00 End: .abort 0 Init 0 1 0 00 Start at 1; Start: .abort (AFTER DELETE ON trace) 1 Null 0 1 0 00 r[1]=NULL 2 OpenRead 2 27 0 k(2,,) 02 root=27 iDb=0; item_idx_01 3 Param 0 2 0 00 r[2]=old.rowid 4 IsNull 2 11 0 00 if r[2]==NULL goto 11 5 Affinity 2 1 0 C 00 affinity(r[2]) 6 SeekGE 2 11 2 1 00 key=r[2] 7 IdxGT 2 11 2 1 00 key=r[2] 8 IdxRowid 2 3 0 00 r[3]=rowid 9 RowSetAdd 1 3 0 00 rowset(1)=r[3] 10 Next 2 7 1 00 11 OpenWrite 0 9 0 6 00 root=9 iDb=0; item 12 OpenWrite 1 27 0 k(2,,) 08 root=27 iDb=0; item_idx_01 13 RowSetRead 1 49 3 00 r[3]=rowset(1) 14 NotExists 0 48 3 1 00 intkey=r[3] 15 Copy 3 4 0 00 r[4]=r[3] 16 Column 0 1 6 00 r[6]=item.traceid 17 Column 0 5 10 00 r[10]=item.nameid 18 FkIfZero 0 26 0 00 if fkctr[0]==0 goto 26 19 IsNull 10 26 0 00 if r[10]==NULL goto 26 20 SCopy 10 11 0 00 r[11]=r[10] 21 MustBeInt 11 25 0 00 22 OpenRead 3 10 0 6 00 root=10 iDb=0; metadata 23 NotExists 3 25 11 00 intkey=r[11] 24 Goto 0 26 0 00 25 FkCounter 0 -1 0 00 fkctr[0]+=-1 26 Close 3 0 0 00 27 FkIfZero 0 35 0 00 if fkctr[0]==0 goto 35 28 IsNull 6 35 0 00 if r[6]==NULL goto 35 29 SCopy 6 11 0 00 r[11]=r[6] 30 MustBeInt 11 34 0 00 31 OpenRead 4 8 0 7 00 root=8 iDb=0; trace 32 NotExists 4 34 11 00 intkey=r[11] 33 Goto 0 35 0 00 34 FkCounter 0 -1 0 00 fkctr[0]+=-1 35 Close 4 0 0 00 36 OpenRead 6 19 0 k(2,,) 02 root=19 iDb=0; metadata_idx_06 37 IsNull 4 43 0 00 if r[4]==NULL goto 43 38 Affinity 4 1 0 C 00 affinity(r[4]) 39 SeekGE 6 43 4 1 00 key=r[4] 40 IdxGT 6 43 4 1 00 key=r[4] 41 FkCounter 0 1 0 00 fkctr[0]+=1 42 Next 6 40 1 00 43 Column 0 1 13 00 r[13]=item.traceid 44 Rowid 0 14 0 00 r[14]=rowid 45 IdxDelete 1 13 2 00 key=r[13..14] 46 Delete 0 1 0 item 00 47 Program 4 0 15 program 00 Call: fkey.abort 48 Goto 0 13 0 00 49 ResetCount 0 0 0 00 50 Halt 0 0 0 00 End: .abort 0 Init 0 1 0 00 Start at 1; Start: .abort (AFTER DELETE ON item) 1 Null 0 1 0 00 r[1]=NULL 2 OpenRead 11 19 0 k(2,,) 02 root=19 iDb=0; metadata_idx_06 3 Param 0 2 0 00 r[2]=old.rowid 4 IsNull 2 11 0 00 if r[2]==NULL goto 11 5 Affinity 2 1 0 C 00 affinity(r[2]) 6 SeekGE 11 11 2 1 00 key=r[2] 7 IdxGT 11 11 2 1 00 key=r[2] 8 IdxRowid 11 3 0 00 r[3]=rowid 9 RowSetAdd 1 3 0 00 rowset(1)=r[3] 10 Next 11 7 1 00 11 OpenWrite 0 10 0 6 00 root=10 iDb=0; metadata 12 OpenWrite 1 23 0 k(5,NOCASE,,,,) 08 root=23 iDb=0; metadata_idx_10 13 OpenWrite 2 22 0 k(3,NOCASE,,) 08 root=22 iDb=0; metadata_idx_09 14 OpenWrite 3 21 0 k(3,,NOCASE,) 08 root=21 iDb=0; metadata_idx_08 15 OpenWrite 4 20 0 k(3,,NOCASE,) 08 root=20 iDb=0; metadata_idx_07 16 OpenWrite 5 19 0 k(2,,) 08 root=19 iDb=0; metadata_idx_06 17 OpenWrite 6 18 0 k(3,,,) 08 root=18 iDb=0; metadata_idx_05 18 OpenWrite 7 17 0 k(3,,,) 08 root=17 iDb=0; metadata_idx_04 19 OpenWrite 8 16 0 k(2,,) 08 root=16 iDb=0; metadata_idx_03 20 OpenWrite 9 15 0 k(3,,,) 08 root=15 iDb=0; metadata_idx_02 21 OpenWrite 10 14 0 k(6,NOCASE,NOCASE,,,,) 08 root=14 iDb=0; metadata_idx_01 22 RowSetRead 1 101 3 00 r[3]=rowset(1) 23 NotExists 0 100 3 1 00 intkey=r[3] 24 Copy 3 4 0 00 r[4]=r[3] 25 Column 0 3 8 NULL 00 r[8]=metadata.datasetid 26 Column 0 4 9 NULL 00 r[9]=metadata.traceid 27 Column 0 5 10 NULL 00 r[10]=metadata.itemid 28 FkIfZero 0 36 0 00 if fkctr[0]==0 goto 36 29 IsNull 10 36 0 00 if r[10]==NULL goto 36 30 SCopy 10 11 0 00 r[11]=r[10] 31 MustBeInt 11 35 0 00 32 OpenRead 12 9 0 6 00 root=9 iDb=0; item 33 NotExists 12 35 11 00 intkey=r[11] 34 Goto 0 36 0 00 35 FkCounter 0 -1 0 00 fkctr[0]+=-1 36 Close 12 0 0 00 37 FkIfZero 0 45 0 00 if fkctr[0]==0 goto 45 38 IsNull 9 45 0 00 if r[9]==NULL goto 45 39 SCopy 9 11 0 00 r[11]=r[9] 40 MustBeInt 11 44 0 00 41 OpenRead 13 8 0 7 00 root=8 iDb=0; trace 42 NotExists 13 44 11 00 intkey=r[11] 43 Goto 0 45 0 00 44 FkCounter 0 -1 0 00 fkctr[0]+=-1 45 Close 13 0 0 00 46 FkIfZero 0 54 0 00 if fkctr[0]==0 goto 54 47 IsNull 8 54 0 00 if r[8]==NULL goto 54 48 SCopy 8 11 0 00 r[11]=r[8] 49 MustBeInt 11 53 0 00 50 OpenRead 14 7 0 3 00 root=7 iDb=0; dataset 51 NotExists 14 53 11 00 intkey=r[11] 52 Goto 0 54 0 00 53 FkCounter 0 -1 0 00 fkctr[0]+=-1 54 Close 14 0 0 00 55 OpenRead 15 9 0 6 00 root=9 iDb=0; item 56 Rewind 15 61 0 00 57 Column 15 5 11 00 r[11]=item.nameid 58 Ne 4 60 11 (BINARY) 53 if r[11]!=r[4] goto 60 59 FkCounter 0 1 0 00 fkctr[0]+=1 60 Next 15 57 0 01 61 Column 0 1 13 00 r[13]=metadata.parameter 62 Column 0 3 14 NULL 00 r[14]=metadata.datasetid 63 Column 0 4 15 NULL 00 r[15]=metadata.traceid 64 Column 0 5 16 NULL 00 r[16]=metadata.itemid 65 Rowid 0 17 0 00 r[17]=rowid 66 IdxDelete 1 13 5 00 key=r[13..17] 67 Column 0 4 14 NULL 00 r[14]=metadata.traceid 68 Rowid 0 15 0 00 r[15]=rowid 69 IdxDelete 2 13 3 00 key=r[13..15] 70 Column 0 4 13 NULL 00 r[13]=metadata.traceid 71 Column 0 1 14 00 r[14]=metadata.parameter 72 IdxDelete 3 13 3 00 key=r[13..15] 73 Column 0 3 13 NULL 00 r[13]=metadata.datasetid 74 IdxDelete 4 13 3 00 key=r[13..15] 75 Column 0 5 13 NULL 00 r[13]=metadata.itemid 76 Rowid 0 14 0 00 r[14]=rowid 77 IdxDelete 5 13 2 00 key=r[13..14] 78 Column 0 4 18 NULL 00 r[18]=metadata.traceid 79 Column 0 5 19 NULL 00 r[19]=metadata.itemid 80 Rowid 0 20 0 00 r[20]=rowid 81 IdxDelete 6 18 3 00 key=r[18..20] 82 Column 0 3 18 NULL 00 r[18]=metadata.datasetid 83 IdxDelete 7 18 3 00 key=r[18..20] 84 Column 0 4 18 NULL 00 r[18]=metadata.traceid 85 Rowid 0 19 0 00 r[19]=rowid 86 IdxDelete 8 18 2 00 key=r[18..19] 87 Column 0 3 21 NULL 00 r[21]=metadata.datasetid 88 Column 0 4 22 NULL 00 r[22]=metadata.traceid 89 Rowid 0 23 0 00 r[23]=rowid 90 IdxDelete 9 21 3 00 key=r[21..23] 91 Column 0 1 24 00 r[24]=metadata.parameter 92 Column 0 2 25 00 r[25]=metadata.value 93 Column 0 3 26 NULL 00 r[26]=metadata.datasetid 94 Column 0 4 27 NULL 00 r[27]=metadata.traceid 95 Column 0 5 28 NULL 00 r[28]=metadata.itemid 96 Rowid 0 29 0 00 r[29]=rowid 97 IdxDelete 10 24 6 00 key=r[24..29] 98 Delete 0 1 0 metadata 00 99 Program 4 0 30 program 00 Call: fkey.abort 100 Goto 0 22 0 00 101 ResetCount 0 0 0 00 102 Halt 0 0 0 00 End: .abort _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
