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 <wout.mert...@gmail.com> 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 <n...@cryptonector.com> 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@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
