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?
Advertising
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
