Re: [sqlite] calculated-value indexes are not covering?

That clears it up, many thanks! On Fri, Aug 11, 2017 at 2:32 PM Richard Hipp wrote: > On 8/11/17, Wout Mertens wrote: > > Aha ok, great! > > > > Now, forgive me, but there is still a difference in the byte code, and > I'm > > having a hard time

Re: [sqlite] calculated-value indexes are not covering?

On 8/11/17, Wout Mertens wrote: > Aha ok, great! > > Now, forgive me, but there is still a difference in the byte code, and I'm > having a hard time decyphering it. The difference from the non-distinct and > the distinct is: > > * The table t1 is opened Yes, there is a

Re: [sqlite] calculated-value indexes are not covering?

Aha ok, great! Now, forgive me, but there is still a difference in the byte code, and I'm having a hard time decyphering it. The difference from the non-distinct and the distinct is: * The table t1 is opened * There is an extra Seek in the scanning loop, it looks like it moves the read pointer

Re: [sqlite] calculated-value indexes are not covering?

On 8/11/17, Wout Mertens wrote: > So, am I correct in thinking that an index on expressions already has all > the required data to answer e.g. a SELECT DISTINCT? > > If so, that could be an optimization? > > Can I request this optimization to be made? :) > That

Re: [sqlite] calculated-value indexes are not covering?

So, am I correct in thinking that an index on expressions already has all the required data to answer e.g. a SELECT DISTINCT? If so, that could be an optimization? Can I request this optimization to be made? :) Thanks, Wout. On Thu, Aug 10, 2017, 7:47 AM Wout Mertens

Re: [sqlite] calculated-value indexes are not covering?

Nico, I respectfully disagree, if you look at my first post you can see that the first query does consider that single value index on s covering. Indeed all the indexes here have all the required data to be covering for their queries. As David says, it seems there is a missed optimization

Re: [sqlite] calculated-value indexes are not covering?

On Wed, Aug 09, 2017 at 06:59:18PM +, Wout Mertens wrote: > 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? No,

Re: [sqlite] calculated-value indexes are not covering?

f 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 sa

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

Re: [sqlite] calculated-value indexes are not covering?

On Wed, Aug 09, 2017 at 06:48:51PM +, 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

[sqlite] calculated-value indexes are not covering?

Back with more indexing questions :) 12991 $ sqlite3 SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(j json, s string); sqlite> create index s on