One of your delete triggers is causing a table scan of table item. Multiple scans of table item, for each row of dataset. From the code it looks like it is scanning for a match on "nameid". And there is not index on item(nameid ...) ...
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Thomas Kurz >Sent: Thursday, 31 October, 2019 16:54 >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
