I had nearly the same question a month ago (Subject: Index usefulness for GROUP BY). In my case, the best index was on the WHERE clause because it eliminated the scan and returned only the few important rows for the other clauses. However, the best result will depend on how many rows are eliminated compared to the number of rows remaining for the GROUP BY to deal with.
Jeff > On Apr 2, 2017, at 6:27 PM, Hamish Allan <ham...@gmail.com> wrote: > > 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
