I noticed that the query planner favours the primary index when a covering
index exists which can satisfy the same query.

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?

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?

*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=?)

*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=?)

I also noticed that when using > or < instead of = that the covering index
is used instead of the primary index.

Could someone please help me to understand why it works like this?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to