On 5/15/18, Peter Johnson <pe...@missinglink.co.nz> wrote: > > My understanding is that covering indices are more efficient, as the table > itself does not need to be scanned when all the required columns exist in > the covering index?
That is often the case, but there are exceptions. > > Is it correct to say that example 1 is more efficient than the other two > examples, particularly when there are many columns, all of which are > covered by the covering index? No. > > *example 1 - using covering index* > > CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER); > CREATE INDEX idx_covering ON example( a, b, c ); > EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1; > > 0|0|0|SEARCH TABLE example USING COVERING INDEX idx_covering (a=? AND b=?) > > *example2 - using primary key & covering index* > > CREATE TABLE example (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER); > CREATE INDEX idx_covering ON example( a, b, c ); > EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1; > > 0|0|0|SEARCH TABLE example USING INTEGER PRIMARY KEY (rowid=?) An INTEGER PRIMARY KEY *is* a covering index, even though the EXPLAIN QUERY PLAN output does not identify it as such. > > *example3 - using composite primary key & covering index* > > CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER, PRIMARY KEY(a, b)); > CREATE INDEX idx_covering ON example( a, b, c ); > EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1; > > 0|0|0|SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (a=? AND > b=?) In this case, you have faked out the query planner. The query planner prefers to use the index created to implement the PRIMARY KEY since it is a UNIQUE index that will only return a single row. The covering index, on the other hand, might return multiple rows as far as the query planner knows. The query planner fails to deduce that the first two columns of the idx_covering index will be unique due to the existence of the first index. A better approach here would be to make the PRIMARY KEY a true PRIMARY KEY, and not just an alias for a UNIQUE constraint, but adding WITHOUT ROWID to the end of the CREATE TABLE statement. The PRIMARY KEY on a WITHOUT ROWID table is always a covering index (even though the EXPLAIN QUERY PLAN output does not say so) so you will always end up using a covering index. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
