Given the SQL below, FooX is a covered index for x on Foo. I want to create FooXB as a second index on x in Foo. Since 'x' is covered on FooX it should be cheaper to build FooXB from index FooX, than from table Foo. However, as far as I can tell from the from the opcodes of the index creation it doesn't do this (OpenRead uses rootpage=2 instead of 3). Is my understanding correct?
And if my understanding is correct, is there any scenarios in which I can
coerce SQLITE to build a new index based on data in an existing index?
drop table Foo;
create table Foo(x text, y text, z text);
insert into Foo(x) values("elephant");
insert into Foo(x) values("cat");
insert into Foo(x) values("giraffe");
insert into Foo(x) values("dog");
insert into Foo(x) values("zebra");
insert into Foo(x) values("lion");
insert into Foo(x) values("panther");
create index FooX on Foo(x);
create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
select * from sqlite_master;
RecNo type name tbl_name rootpage sql
----- ----- ---- -------- -------- ----------------------------------------
1 table Foo Foo 2 CREATE TABLE Foo(x text, y text, z text)
2 index FooX Foo 3 CREATE INDEX FooX on Foo(x)
explain create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
RecNo addr opcode p1 p2 p3 p4
p5 comment
----- ---- ------------ -- -- --
---------------------------------------------------------------------------- --
-------
1 0 Init 0 37 0
00 (null)
2 1 Noop 0 36 0
00 (null)
3 2 CreateBtree 0 1 2
00 (null)
4 3 OpenWrite 0 1 0 5
00 (null)
5 4 NewRowid 0 2 0
00 (null)
6 5 String8 0 3 0 index
00 (null)
7 6 String8 0 4 0 FooXB
00 (null)
8 7 String8 0 5 0 Foo
00 (null)
9 8 Copy 1 6 0
00 (null)
10 9 String8 0 7 0 CREATE INDEX FooXB on Foo(substr(x,2,1)) where
substr(x,2,1) > 'e' (more...) 00 (null)
11 10 MakeRecord 3 5 8 BBBDB
00 (null)
12 11 Insert 0 8 2
18 (null)
13 12 SorterOpen 3 0 1 k(2,,)
00 (null)
14 13 OpenRead 1 2 0 3
00 (null)
15 14 Rewind 1 24 0
00 (null)
16 15 Column 1 0 11
00 (null)
17 16 PureFunc0 6 11 10 substr(3)
03 (null)
18 17 Le 14 23 10
51 (null)
19 18 Column 1 0 17
00 (null)
20 19 PureFunc0 6 17 15 substr(3)
03 (null)
21 20 Rowid 1 16 0
00 (null)
22 21 MakeRecord 15 2 9
00 (null)
23 22 SorterInsert 3 9 0
00 (null)
24 23 Next 1 15 0
00 (null)
25 24 OpenWrite 2 1 0 k(2,,)
11 (null)
26 25 SorterSort 3 30 0
00 (null)
27 26 SorterData 3 9 2
00 (null)
28 27 SeekEnd 2 0 0
00 (null)
29 28 IdxInsert 2 9 0
10 (null)
30 29 SorterNext 3 26 0
00 (null)
31 30 Close 1 0 0
00 (null)
32 31 Close 2 0 0
00 (null)
33 32 Close 3 0 0
00 (null)
34 33 SetCookie 0 1 43
00 (null)
35 34 ParseSchema 0 0 0 name='FooXB' AND type='index'
00 (null)
36 35 Expire 0 1 0
00 (null)
37 36 Halt 0 0 0
00 (null)
38 37 Transaction 0 1 42 0
01 (null)
39 38 Integer 2 12 0
00 (null)
40 39 Integer 1 13 0
00 (null)
41 40 String8 0 14 0 e
00 (null)
42 41 Integer 2 18 0
00 (null)
43 42 Integer 1 19 0
00 (null)
44 43 Goto 0 1 0
00 (null)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
