SQLite: indices

Covering indices

A covering index is an index that contains all data for a specific select statement. In such a case, SQLite doesn't have to visit the indexed table:

create table tq84_data (
  col1 text,
  col2 text,
  col3 text,
  col4 text
);

create index tq84_ix_covering on tq84_data(col1, col2, col3);

explain query plan
select col3 from tq84_data
where
  col1 = 'foo' and
  col2 = 'bar';
--
--  0|0|0|SEARCH TABLE tq84_data USING COVERING INDEX tq84_ix_covering (col1=? AND col2=?)

drop table tq84_data;

Github repository about-sqlite, path: /indices/covering-index.sql

Misc

It's not possible to create indices on virtual tables in SQLite;

SQLite: indices

Covering indices

Misc

See also