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
