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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
