On 2019/04/30 2:10 AM, Deon Brewis wrote:
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?
Not quite. This is a good example of something that "feels" like it
should be better, just isn't.
Unless Foo(x) is a partial Index and the new index can somehow indicate
that it has the same partiality as the original index (which it can't
unless it's exactly equal, in which case, it's useless), there can be no
advantage.
Keep in mind that, in SQLite, a table is nothing less than a covering
Index itself with the row_id as the indexer (or the actual PK in the
case of WITHOUT ROWID tables). There is no reason why it itself (being
an Index) should be any slower to "walk" than any other Index, in fact a
lookup via any other index will include an extra step (the lookup
itself) that you don't have when walking the table index itself (aka
doing a "table scan"). It's just better for anything where you access
any field that are not in the existing index, and not worse for those
that are.
There might be a small but real advantage if the field (that was indexed
on) appeared at the end of very long list of fields or very large
fields, i.e. hidden at the back end of the column list with really large
(long-to-read) columns preceding it - meaning the existing Index would
already have singled out that bit of data - but it's a very small
likelihood and use-case though. (Meaning that it's unlikely for people
to make multiple Indexes on the same field(s), so investing the effort
and code-bloat in catering for the optimization it, which would only
ever benefit it in the case where the column IS at the back of big other
columns, would be of dubious benefit).
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';
As an aside - if your INSERTs above was a "find the odd one out" puzzle,
I vote that the answer would be "panther". :)
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
