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:

  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);

sqlite-users mailing list

Reply via email to