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
