SQLite can only use a covering index whose prefix satifies the WHERE and/or 
ORDER BY clause(es).

WHERE x=1
ORDER BY y

The WHERE constraint can be handled by an index that starts off with x.
The ORDER BY can be handled by an index that starts off with y.

SQLite *may* realise that an index on (x,y) satisfies both conditions (within 
the fixed x values, y values are already ordered). In that case you would 
require the _id field to make it a convering index (x,y,_id).

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mark Wagner
Gesendet: Mittwoch, 07. Februar 2018 01:44
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] question about covering index

Given the following schema:

CREATE TABLE foo (_id integer primary key, x, y); CREATE INDEX i on foo(_id, x, 
y);

And the following query

sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id
sqlite> ORDER
BY y;

I would have expected it (hoped?) that it would use the covering index for the 
order by.  Any clue why it doesn't or what I could do differently to get it to 
use an index for the selection, the grouping, and the ordering?

selectid = 0
   order = 0
    from = 0
  detail = SCAN TABLE foo

selectid = 0
   order = 0
    from = 0
  detail = USE TEMP B-TREE FOR ORDER BY
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to