On Sun, 16 Sep 2018 10:59:31 -0400 Richard Hipp <d...@sqlite.org> wrote:
> On 9/16/18, John Found <johnfo...@asm32.info> wrote: > > > > Is it means that in every query that uses GROUP BY and ORDER BY > > simultaneously, one of the operations will always be provided by using > > temporary b-tree? > > > > no. > > CREATE TABLE t1(a,b,c, PRIMARY KEY(a,b)) WITHOUT ROWID; > explain query plan > SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b; > > -- > D. Richard Hipp > d...@sqlite.org BTW, using your example and modifying it in order the GROUP BY to be needed I got some not obvious results: drop table t1; CREATE TABLE t1(a,b,c); insert into t1 values (1, 2, 3), (1,2,1), (1, 2, 2), (2, 2, 1), (2, 1, 2); create index idxT1 on t1(a desc,b desc); -- This one orders ascending by using descending index. Great. explain query plan SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b; id parent notused detail 8 0 0 SCAN TABLE t1 USING INDEX idxT1 SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b; a b sum(c) 1 2 6 2 1 2 2 2 1 -- The following can't order descending by using descending index... :? explain query plan SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a desc, b desc; id parent notused detail 8 0 0 SCAN TABLE t1 USING INDEX idxT1 41 0 0 USE TEMP B-TREE FOR ORDER BY SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a desc, b desc; a b sum(c) 2 2 1 2 1 2 1 2 6 -- But this one orders properly descending, by using descending index, but without ORDER BY clause. explain query plan SELECT a, b, sum(c) FROM t1 GROUP BY a, b; id parent notused detail 7 0 0 SCAN TABLE t1 USING INDEX idxT1 SELECT a, b, sum(c) FROM t1 GROUP BY a, b; a b sum(c) 2 2 1 2 1 2 1 2 6 -- John Found <johnfo...@asm32.info> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
