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