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
