Thanks a lot!
I've just noticed it also has the same behavior for partial indexes.
Here is an explain with a partial index (and using a later build this time for
my output...).
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));
CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42;
explain UPDATE foo SET x=1 WHERE rowid=1;
sqlite> explain UPDATE foo SET x=1 WHERE rowid=1;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 45 0 00 Start at 45
1 Null 0 10 11 00 r[10..11]=NULL
2 OpenWrite 0 2 0 3 00 root=2 iDb=0; Foo
3 Integer 1 15 0 00 r[15]=1
4 SeekRowid 0 6 15 00 intkey=r[15]; pk
5 Rowid 0 11 0 00 r[11]=rowid
6 OpenWrite 1 6 0 k(2,,) 00 root=6 iDb=0;
FooPartialZ
7 OpenWrite 2 5 0 k(2,,) 00 root=5 iDb=0; FooLenZ
8 OpenWrite 4 3 0 k(2,,) 00 root=3 iDb=0; FooX
9 IsNull 11 44 0 00 if r[11]==NULL goto 44
10 Integer 1 12 0 00 r[12]=1
11 Column 0 1 13 00 r[13]=Foo.y
12 Column 0 2 14 00 r[14]=Foo.z
13 Noop 0 0 0 00 uniqueness check for
FooPartialZ
14 Null 0 1 0 00 r[1]=NULL
15 Le 17 19 14 (BINARY) 51 if r[14]<=r[17] goto
19
16 SCopy 14 2 0 00 r[2]=r[14]; z
17 IntCopy 11 3 0 00 r[3]=r[11]; rowid
18 MakeRecord 2 2 1 00 r[1]=mkrec(r[2..3]);
for FooPartialZ
19 Noop 0 0 0 00 uniqueness check for
FooLenZ
20 Copy 14 16 0 00 r[16]=r[14]
21 PureFunc0 0 16 5 length(1) 01 FooLenZ column 0
22 IntCopy 11 6 0 00 r[6]=r[11]; rowid
23 MakeRecord 5 2 4 00 r[4]=mkrec(r[5..6]);
for FooLenZ
24 Noop 0 0 0 00 uniqueness check for
FooX
25 SCopy 12 8 0 00 r[8]=r[12]; x
26 IntCopy 11 9 0 00 r[9]=r[11]; rowid
27 MakeRecord 8 2 7 00 r[7]=mkrec(r[8..9]);
for FooX
28 Le 17 32 14 (BINARY) 51 if r[14]<=r[17] goto
32
29 Column 0 2 18 00 r[18]=Foo.z
30 Rowid 0 19 0 00 r[19]=rowid
31 IdxDelete 1 18 2 00 key=r[18..19]
32 Copy 14 16 0 00 r[16]=r[14]
33 PureFunc0 0 16 18 length(1) 01
34 Rowid 0 19 0 00 r[19]=rowid
35 IdxDelete 2 18 2 00 key=r[18..19]
36 Column 0 0 18 00 r[18]=Foo.x
37 IdxDelete 4 18 2 00 key=r[18..19]
38 IsNull 1 40 0 00 if r[1]==NULL goto 40
39 IdxInsert 1 1 2 2 00 key=r[1]
40 IdxInsert 2 4 5 2 00 key=r[4]
41 IdxInsert 4 7 8 2 00 key=r[7]
42 MakeRecord 12 3 16 00 r[16]=mkrec(r[12..14])
43 Insert 0 16 11 Foo 05 intkey=r[11]
data=r[16]
44 Halt 0 0 0 00
45 Transaction 0 1 5 0 01 usesStmtJournal=0
46 Integer 42 17 0 00 r[17]=42
47 Goto 0 1 0 00
- Deon
-----Original Message-----
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of
Richard Hipp
Sent: Wednesday, September 12, 2018 7:05 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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.
Thanks for bringing this to our attention.
We're on lockdown for the 3.25.0 release (bug fixes only) but I did start
looking at this to see how feasible it would be. I found this
comment:
https://www.sqlite.org/src/artifact/345ce35eb133?ln=306
So apparently this has come up before :-) That comment was inserted on
2015-09-04.
--
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
