AFAIK it is considered good practice to group fields used in indices at the beginning of the table definition (because they tend to get referenced most) and BLOB fields at the end (because acessing fields behind a BLOB - which is "large" by definition - tends to take more effort).
So the optimization would not benefit a "properly designed" schema anyway. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von R Smith Gesendet: Dienstag, 30. April 2019 13:54 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Does SQLITE ever optimize index creation based on another index? 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
