On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote:
> select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1),
> max(ed),target, sum(amt) from Tasks where Pid=1 group by target
> HAVING amt > 0;
When I first read that query I wondered if putting a query inside a
select expression would execute it for each row. I don't know if that
is the case, but here is the query plan:
explain query plan select (SELECT ed from Tasks where task =
'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where
Pid=1 group by target HAVING amt > 0;
0,0,0,"SCAN TABLE Tasks"
0,0,0,"USE TEMP B-TREE FOR GROUP BY"
0,0,0,"EXECUTE SCALAR SUBQUERY 1"
1,0,0,"SCAN TABLE Tasks"
It would appear that moving the subquery down into a FROM clause makes the
query plan look slightly better. In my humble opinion it also makes the
query easier to understand.
explain query plan
select
q.ed,
max(tasks.ed),
target,
sum(amt)
from
(SELECT ed from Tasks where task = 'QUOTE' and Pid = 1) q
inner join
Tasks
on
Pid=1
group by
q.ed,
target
HAVING
amt > 0;
0,0,0,"SCAN TABLE Tasks"
0,1,1,"SEARCH TABLE Tasks USING AUTOMATIC PARTIAL COVERING INDEX
(Pid=?)"
0,0,0,"USE TEMP B-TREE FOR GROUP BY"
The above is the case with version 3.16.2.
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
