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
