I can't seem to get sqlite3 to not use a TEMP B-TREE with a compound index for a query with a LIKE clause and a GROUP BY clause. Seems to only use the index for the WHERE clause. I've tried making the index with all combinations with COLLATE NOCASE in the index since LIKE is case insensitive.
Example would be the following with an index on foo, boo. SELECT foo, boo FROM mytable WHERE foo LIKE 'hi%' GROUP BY boo; Also tried using a subquery such as SELECT foo, boo FROM (SELECT foo, boo FROM mytable WHERE foo LIKE 'hi%') GROUP BY boo; Interesting to note is that ORDER BY works in the similar subquery case above but also does not work with the straight query. Any help appreciated!
