This seems to work perfectly for expression indexes. Thanks, it makes a HUGE difference for us!
Would you perhaps be able to make a similar fix for partial indexes? i.e. this scenario: CREATE TABLE Foo(x, y, z); CREATE INDEX FooX on Foo(x); CREATE INDEX FooZ on Foo(z); CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42; explain UPDATE foo SET x=1 WHERE rowid=1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 36 0 00 Start at 36 1 Null 0 7 8 00 r[7..8]=NULL 2 OpenWrite 0 2 0 3 00 root=2 iDb=0; Foo 3 Integer 1 12 0 00 r[12]=1 4 SeekRowid 0 6 12 00 intkey=r[12] 5 Rowid 0 8 0 00 r[8]=rowid 6 OpenWrite 1 5 0 k(2,,) 00 root=5 iDb=0; FooPartialZ <<<=========================== 7 OpenWrite 3 3 0 k(2,,) 00 root=3 iDb=0; FooX 8 IsNull 8 35 0 00 if r[8]==NULL goto 35 9 Integer 1 9 0 00 r[9]=1 10 Column 0 1 10 00 r[10]=Foo.y 11 Column 0 2 11 00 r[11]=Foo.z 12 Noop 0 0 0 00 uniqueness check for FooPartialZ <<<=========================== 13 Null 0 1 0 00 r[1]=NULL 14 Le 14 18 11 (BINARY) 51 if r[11]<=r[14] goto 18 15 SCopy 11 2 0 00 r[2]=r[11]; z 16 IntCopy 8 3 0 00 r[3]=r[8]; rowid 17 MakeRecord 2 2 1 00 r[1]=mkrec(r[2..3]); for FooPartialZ <<<=========================== 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 Column 0 2 13 00 r[13]=Foo.z 23 Le 14 27 13 (BINARY) 51 if r[13]<=r[14] goto 27 24 Column 0 2 15 00 r[15]=Foo.z 25 Rowid 0 16 0 00 r[16]=rowid 26 IdxDelete 1 15 2 00 key=r[15..16] 27 Column 0 0 15 00 r[15]=Foo.x 28 Rowid 0 16 0 00 r[16]=rowid 29 IdxDelete 3 15 2 00 key=r[15..16] 30 IsNull 1 32 0 00 if r[1]==NULL goto 32 31 IdxInsert 1 1 2 2 00 key=r[1] <<<=========================== 32 IdxInsert 3 4 5 2 00 key=r[4] 33 MakeRecord 9 3 13 00 r[13]=mkrec(r[9..11]) 34 Insert 0 13 8 Foo 05 intkey=r[8] data=r[13] 35 Halt 0 0 0 00 36 Transaction 0 1 4 0 01 usesStmtJournal=0 37 Integer 42 14 0 00 r[14]=42 38 Goto 0 1 0 00 -----Original Message----- From: drhsql...@gmail.com <drhsql...@gmail.com> On Behalf Of Richard Hipp Sent: Saturday, September 15, 2018 2:46 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: de...@outlook.com Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update On 9/11/18, Deon Brewis <de...@outlook.com> wrote: > It seems like there is an opportunity for improvement on updates if an > index contains expressions. This enhancement did not make the cutoff for 3.25.0. But as 3.25.0 is now out, I have started the next release cycle and you can find this enhancement on the latest trunk version of SQLite. You'll need to grab a tarball (or clone the Fossil repository) and compile it yourself. If you can, please do this and try out the code and let me know whether or not it works, that will be appreciated. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
