Hello,
> explain query plan select > prod, > per, > min(val) > from > (select > prod, > per, > mar, > sum(val) as val > from > data > group by > prod, > per, > mar) > group by > prod, > per > ; > QUERY PLAN > |--CO-ROUTINE 1 > | `--SCAN TABLE data USING INDEX sqlite_autoindex_data_1 > |--SCAN SUBQUERY 1 > `--USE TEMP B-TREE FOR GROUP BY I constructed an equivalent query that does not involve a temporary B-tree. I don't expect this to be useful for the real case. But it proves that speed can be improved. With 1000*100*10*1 rows in the data table, my timing was reduced from 0.859 to 0.635. E. Pasma explain query plan select prod, per, (select min(val) from (select sum(val) as val from data where (prod,per)=(v1.prod,v1.per) group by prod, per, mar)) from (select prod, per from data group by prod, per) v1 ; QUERY PLAN |--CO-ROUTINE 3 | `--SCAN TABLE data USING COVERING INDEX sqlite_autoindex_data_1 |--SCAN SUBQUERY 3 AS v1 `--CORRELATED SCALAR SUBQUERY 2 |--CO-ROUTINE 1 | `--SEARCH TABLE data USING INDEX sqlite_autoindex_data_1 (prod=? AND per=?) `--SEARCH SUBQUERY 1 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users