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
