What is the purpose of the ORDER BY? The value of a used for the order by is from some random row in the grouping of c. Are there relationships between a, b, c, d that you have not documented nor told us about?
In any case, your most efficient index is on (b, c). The order by is useless. > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Hamish Allan > Sent: Sunday, 2 April, 2017 17:28 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY > > Given a table: > > CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); > > the query: > > SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; > > shows the following plan, without indexes: > > 0|0|0|SCAN TABLE x > 0|0|0|USE TEMP B-TREE FOR GROUP BY > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > I can create an index to cover the WHERE clause: > > CREATE INDEX b_index ON x (b); > > which gives the plan: > > 0|0|0|SEARCH TABLE x USING INDEX b_index (b=?) > 0|0|0|USE TEMP B-TREE FOR GROUP BY > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > or I can create an index to cover the GROUP BY clause: > > DROP INDEX b_index; > CREATE INDEX c_index ON x (c); > > which gives the plan: > > 0|0|0|SCAN TABLE x USING INDEX c_index > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > but I don't seem to be able to create a combined index to cover both > the WHERE and GROUP BY clauses (let alone the ORDER BY clause). > > Am I missing something, or is this just not possible? > > If it's not possible, which is the more efficient of those indexes -- > or is there a third way, using an index for the ORDER BY, which would > be more efficient still? > > Thanks, > Hamish > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
