There's the issue of whether SQLite takes the value from the index, or recalculates it from the table data. So for a "covering index" you would need to index all the inputs to the function, for example
sqlite> create index lc on t (length(s), s); sqlite> explain query plan select distinct length(s) from t; selectid|order|from|detail 0|0|0|SCAN TABLE t USING COVERING INDEX lc If you look at the explain output you can see that the expression index is still opening the main table in addition to the index. I believe using the actual value stored in the index is either a future optimization, or only works for functions which are explicitly marked as deterministic. Someone else can provide some more light on that. sqlite> explain select distinct length(s) from t indexed by l; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 13 0 00 Start at 13 1 Null 1 2 0 08 r[2]=NULL 2 OpenRead 0 2 0 2 00 root=2 iDb=0; t 3 OpenRead 2 4 0 k(2,,) 00 root=4 iDb=0; l 4 Explain 0 0 0 SCAN TABLE t USING INDEX l 00 5 Rewind 2 12 1 0 00 6 DeferredSeek 2 0 0 00 Move 0 to 2.rowid if needed 7 Column 2 0 1 00 r[1]= 8 Eq 1 11 2 80 if r[2]==r[1] goto 11 9 Copy 1 2 0 00 r[2]=r[1] 10 ResultRow 1 1 0 00 output=r[1] 11 Next 2 6 0 01 12 Halt 0 0 0 00 13 Transaction 0 0 4 0 01 usesStmtJournal=0 14 Goto 0 1 0 00 Run Time: real 0.020 user 0.000000 sys 0.000000 sqlite> explain select distinct length(s) from t indexed by lc; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 11 0 00 Start at 11 1 Null 1 2 0 08 r[2]=NULL 2 OpenRead 2 5 0 k(3,,,) 00 root=5 iDb=0; lc 3 Explain 0 0 0 SCAN TABLE t USING COVERING INDEX lc 00 4 Rewind 2 10 1 0 00 5 Column 2 0 1 00 r[1]= 6 Eq 1 9 2 80 if r[2]==r[1] goto 9 7 Copy 1 2 0 00 r[2]=r[1] 8 ResultRow 1 1 0 00 output=r[1] 9 Next 2 5 0 01 10 Halt 0 0 0 00 11 Transaction 0 0 4 0 01 usesStmtJournal=0 12 Goto 0 1 0 00 Run Time: real 0.019 user 0.000000 sys 0.000000 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens Sent: Wednesday, August 09, 2017 2:59 PM To: SQLite mailing list Subject: Re: [sqlite] calculated-value indexes are not covering? but… index s is covering and only includes the field s? I thought a covering index was one where all the data needed to satisfy the query is in index? I would say that all the indexes here conform to that definition? https://sqlite.org/optoverview.html 8.0 covering index > If, however, all columns that were to be fetched from the table are already available in the index itself, SQLite will use the values contained in the index and will never look up the original table row On Wed, Aug 9, 2017 at 8:55 PM Nico Williams <n...@cryptonector.com> wrote: > On Wed, Aug 09, 2017 at 06:48:51PM +0000, Wout Mertens wrote: > > sqlite> create table t(j json, s string); > > sqlite> create index s on t(s); > > sqlite> create index j on t(json_extract(j, '$.foo')); > > sqlite> create index l on t(length(s)); > > In order for any of these indices to be covering indices you need to add > all the columns of the table t to them: > > sqlite> create table t(j json, s string); > sqlite> create index s on t(s, j); > sqlite> create index j on t(json_extract(j, '$.foo'), j, s); > sqlite> create index l on t(length(s), s, j); > > Nico > -- > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
