AFAIK, SQLite's optimizer doesn't unfold inner statements to get one query that wouldn't require to store temporary data. So in your query SQLite needs temporary space to store results of 2 inner queries. Then it joins these results and most probably it does it not so effectively as you might think. So you need to re-write your query so that it doesn't contain inner selects but first eliminate errors (such as selecting B.a) and undefined behavior (such as SELECT b FROM tbl GROUP BY a).
Pavel On Wed, Oct 14, 2009 at 5:12 AM, Wenton Thomas <[email protected]> wrote: > sqlite perform group by and order by using transient index, > and if there isn't exist such an index, sqlite will create the index > and > store it in its a temporary file. > > So I think the following SQL statement won't create temporary file in > disk. > > create table tbl(a,b,c); > create index on tbl(a); > create index on tbl(b); > > SELECT B.a,B.b,B.c FROM > (SELECT a,b FROM tbl GROUP BY a) A > INNER JOIN > (SELECT b,c FROM tbl ORDER BY b)B > ON A.b=B.b > > Because both group by and order by could make use of appropriate index > . > > But in my test, temporary file appear. > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
