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

Reply via email to