RSmith wrote: > On 2013/07/21 12:01, E.Pasma wrote: >> Only the execution plan of this query is not optimal: >> 0|0|0|SCAN TABLE categories (~1000000 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 >> 1|0|0|SCAN TABLE ot AS ot1 (~333333 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 >> 2|0|0|SCAN TABLE ot AS ot2 (~333333 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 >> 3|0|0|SCAN TABLE ot AS ot1 (~333333 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4 >> 4|0|0|SCAN TABLE ot AS ot2 (~333333 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5 >> 5|0|0|SCAN TABLE ot AS ot1 (~333333 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6 >> 6|0|0|SCAN TABLE ot AS ot2 (~333333 rows) >> 0|0|0|USE TEMP B-TREE FOR ORDER BY > > Well yes, the plan does not read like one would expect an optimal plan > to read like - but to the purpose of the original request there is no > more-optimal a plan, is there?
SQLite always attempts to flatten such subqueries: <http://www.sqlite.org/optoverview.html#flattening> However, this can be counterproductive when the values in the subquery are subquery lookups themselves; storing the values once in a temporary table might be faster. To prevent SQLite from flattening, break one of the rules from the link above; for example, add an OFFSET clause: select id, a, b, 1.0 * a / b as c from ( select id, (select sum(v) from ot as ot1 where ot1.v > categories.id) as a, (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b from categories limit -1 offset 0 ) order by c; 1|0|0|SCAN TABLE categories (~1000000 rows) 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SCAN TABLE ot AS ot1 (~333333 rows) 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 3|0|0|SCAN TABLE ot AS ot2 (~333333 rows) 0|0|0|SCAN SUBQUERY 1 (~4294967295 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
