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? :)



On Thu, Aug 10, 2017, 7:47 AM Wout Mertens <> wrote:

> 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 opportunity here.
> When looking at the EXPLAIN output, the non-covering index version does not
> seem to actually use the table value it is copying, but I'm having a hard
> time decyphering it.
> On large tables, this is the difference between a 4ms search and a 50ms
> search…
> On Wed, Aug 9, 2017, 9:29 PM Nico Williams <> wrote:
>> On Wed, Aug 09, 2017 at 06:59:18PM +0000, 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, "covering" means that the columns listed in the index include all
>> the columns from the source table that you need for a given query:
>>   CREATE TABLE t(j TEXT, s TEXT, foo TEXT);
>>   SELECT s, j FROM t WHERE s = 'foo'; -- full table scan bc [s] is not
>>                                       -- indexed, is not a PRIMARY KEY,
>>                                       -- and is not UNIQUE
>>   CREATE INDEX t1 ON t(s);
>>   SELECT s FROM t WHERE s = 'foo';    -- uses index; index covers column
>>                                       -- selection (just [s])
>>   SELECT s, j FROM t WHERE s = 'foo'; -- full table scan unless [s] is
>>                                       -- a PRIMARY KEY
>>   CREATE INDEX t2 ON t(j, s);
>>   SELECT s, j FROM t WHERE s = 'foo'; -- full table scan; t2 doesn't
>>                                       -- help because we need a covering
>>                                       -- index where [s] is a prefix
>>   CREATE INDEX t3 ON t(s, j);
>>   SELECT s, j FROM t WHERE s = 'foo'; -- uses covering index t3, finally
>>   SELECT s, j, foo FROM t WHERE s = 'foo'; -- t3 does not cover -> full
>>                                            -- table scan
>> Usually you should have a PRIMARY KEY, and if [s] were one here, then
>> none of these would need full table scans, but only two of these would
>> use only an index and not also index the table via the PK.
>>   -- truly covering index, but only usable in queries by [s] or [s],
>>   -- [j], or [s], [j], [foo]:
>>   CREATE INDEX t4 ON t(s, j, foo);
>> Nico
>> --
>> _______________________________________________
>> sqlite-users mailing list
sqlite-users mailing list

Reply via email to