On 18-2-2019 14:51, Simon Slavin wrote:
On 18 Feb 2019, at 1:33pm, Rocky Ji <rockyji3...@gmail.com> wrote:
Here's the new thing: https://pastebin.com/raw/pSqjvJdZ
Again, can we get rid of them sub-query?
The "sum()" with the "group by" in the subquery make it difficult.
There's nothing wrong with that query just as it is. In that form SQLite will
figure out a very efficient query plan for what you're trying to do. Don't
worry about there being a subquery in there.
Simon.
Indeed, there's nothing wrong with the sub-query, but it 'can' be done
without it. This 'example' has way too few records to test what
performance will do when using the 'without sub-query', compared to the
simple variant.
sqlite> select t1.i, t1.tekst, (select sum(t2.i) from test t2 where
t2.tekst=t1.tekst) as totals from test t1;
QUERY PLAN
|--SCAN TABLE test AS t1
`--CORRELATED SCALAR SUBQUERY 1
`--SCAN TABLE test AS t2
i tekst tota
---- ------------- ----
1 a 5
2 b 2
3 c 3
4 a 5
sqlite> select t1.i, t1.tekst, sum(t2.i) from test t1,test t2 where
t2.tekst=t1.tekst group by t1.i,t1.tekst;
QUERY PLAN
|--SCAN TABLE test AS t1
|--SEARCH TABLE test AS t2 USING AUTOMATIC COVERING INDEX (tekst=?)
`--USE TEMP B-TREE FOR GROUP BY
i tekst sum(
---- ------------- ----
1 a 5
2 b 2
3 c 3
4 a 5
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
