This last one does appear faster... Thanks.
-----Original Message----- From: no...@null.net Sent: Wednesday, October 18, 2017 3:17 PM To: SQLite mailing list Subject: Re: [sqlite] Grouping and grabbing one item
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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
