Just showing with "explain comments" enabled.
sqlite> explain update foo set x = 1 where rowid = 1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 33 0 00 Start at 33 1 Null 0 7 8 00 r[7..8]=NULL 2 OpenWrite 0 2 0 3 00 root=2 iDb=0; Foo 3 Explain 3 0 0 SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) 00 4 Integer 1 12 0 00 r[12]=1 5 SeekRowid 0 7 12 00 intkey=r[12]; pk 6 Rowid 0 8 0 00 r[8]=rowid 7 OpenWrite 1 5 0 k(2,,) 00 root=5 iDb=0; FooLenZ 8 OpenWrite 3 3 0 k(2,,) 00 root=3 iDb=0; FooX 9 IsNull 8 32 0 00 if r[8]==NULL goto 32 10 Integer 1 9 0 00 r[9]=1 11 Column 0 1 10 00 r[10]=Foo.y 12 Column 0 2 11 00 r[11]=Foo.z 13 Noop 0 0 0 00 uniqueness check for FooLenZ 14 Copy 11 13 0 00 r[13]=r[11] 15 PureFunc0 0 13 2 length(1) 01 FooLenZ column 0 16 IntCopy 8 3 0 00 r[3]=r[8]; rowid 17 MakeRecord 2 2 1 00 r[1]=mkrec(r[2..3]); for FooLenZ 18 Noop 0 0 0 00 uniqueness check for FooX 19 SCopy 9 5 0 00 r[5]=r[9]; x 20 IntCopy 8 6 0 00 r[6]=r[8]; rowid 21 MakeRecord 5 2 4 00 r[4]=mkrec(r[5..6]); for FooX 22 Copy 11 13 0 00 r[13]=r[11] 23 PureFunc0 0 13 14 length(1) 01 24 Rowid 0 15 0 00 r[15]=rowid 25 IdxDelete 1 14 2 00 key=r[14..15] 26 Column 0 0 14 00 r[14]=Foo.x 27 IdxDelete 3 14 2 00 key=r[14..15] 28 IdxInsert 1 1 2 2 00 key=r[1] 29 IdxInsert 3 4 5 2 00 key=r[4] 30 MakeRecord 9 3 13 00 r[13]=mkrec(r[9..11]) 31 Insert 0 13 8 Foo 05 intkey=r[8] data=r[13] 32 Halt 0 0 0 00 33 Transaction 0 1 4 0 01 usesStmtJournal=0 34 Goto 0 1 0 00 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Deon Brewis Sent: Tuesday, September 11, 2018 8:20 PM To: SQLite mailing list Subject: [sqlite] SQLITE touches unchanged expression indexes on update It seems like there is an opportunity for improvement on updates if an index contains expressions. In the following example: CREATE TABLE Foo(x, y, z); CREATE INDEX FooX on Foo(x); CREATE INDEX FooZ on Foo(z); CREATE INDEX FooLenZ on Foo(length(z)); explain UPDATE foo SET x=1 WHERE rowid=1; I see the plan below. Notice it's updating FooLenZ even though the 'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It doesn't try to update FooZ, just FooLenZ, but both should be untouched. RecNo addr opcode p1 p2 p3 p4 p5 comment ----- ---- ----------- -- -- -- --------- -- ------- 1 0 Init 0 30 0 00 (null) 2 1 Null 0 7 8 00 (null) 3 2 OpenWrite 0 2 0 3 00 (null) // opening 'Foo' (expected) 4 3 Integer 1 12 0 00 (null) 5 4 SeekRowid 0 6 12 00 (null) 6 5 Rowid 0 8 0 00 (null) 7 6 OpenWrite 1 5 0 k(2,,) 00 (null) // opening 'FooLenZ' (NOT expected) 8 7 OpenWrite 3 3 0 k(2,,) 00 (null) // opening 'FooX' (expected) 9 8 IsNull 8 29 0 00 (null) 10 9 Integer 1 9 0 00 (null) 11 10 Column 0 1 10 00 (null) 12 11 Column 0 2 11 00 (null) 13 12 Copy 11 13 0 00 (null) 14 13 Function0 0 13 2 length(1) 01 (null) 15 14 IntCopy 8 3 0 00 (null) 16 15 MakeRecord 2 2 1 00 (null) 17 16 SCopy 9 5 0 00 (null) 18 17 IntCopy 8 6 0 00 (null) 19 18 MakeRecord 5 2 4 00 (null) 20 19 Copy 11 13 0 00 (null) 21 20 Function0 0 13 14 length(1) 01 (null) 22 21 Rowid 0 15 0 00 (null) 23 22 IdxDelete 1 14 2 00 (null) 24 23 Column 0 0 14 00 (null) 25 24 IdxDelete 3 14 2 00 (null) 26 25 IdxInsert 1 1 2 2 00 (null) // updating 'FooLenZ' (NOT expected) 27 26 IdxInsert 3 4 5 2 00 (null) // updating 'FooX' (expected) 28 27 MakeRecord 9 3 13 00 (null) 29 28 Insert 0 13 8 Foo 05 (null) 30 29 Halt 0 0 0 00 (null) 31 30 Transaction 0 1 42 0 01 (null) 32 31 Goto 0 1 0 00 (null) sqlite_master: RecNo type name tbl_name rootpage sql ----- ----- ------- -------- -------- -------------------------------------- 1 table Foo Foo 2 CREATE TABLE Foo(x, y, z) 2 index FooX Foo 3 CREATE INDEX FooX on Foo(x) 3 index FooZ Foo 4 CREATE INDEX FooZ on Foo(z) 4 index FooLenZ Foo 5 CREATE INDEX FooLenZ on Foo(length(z)) _______________________________________________ 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
