Hi,
I have been thinking about a question on stackoverflow
(http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by),
where some SQL framework removes duplicates from results using GROUP BY
instead of DISTINCT.
I don't want to discuss that this might not be a good idea. However, the
core of that problem is the creation of temp b-trees when using ORDER BY
... DESC after GROUP BY.
I wondered if the construction of a temp b-tree in the third query is
intentional / by design?
I am using sqlite 3.8.1.
sqlite> PRAGMA legacy_file_format=OFF;
sqlite> create table test1 (x INTEGER);
sqlite> create index test1_idx on test1(x);
sqlite> explain query plan select x from test1 group by x order by x;
selectid order from detail
---------- ---------- ----------
-----------------------------------------------
0 0 0 SCAN TABLE test1 USING COVERING
INDEX test1_idx
create table test2 (x INTEGER);
sqlite> create index test2_idx on test2(x);
sqlite> explain query plan select x from test2 group by x order by x desc;
selectid order from detail
---------- ---------- ----------
-----------------------------------------------
0 0 0 SCAN TABLE test2 USING COVERING
INDEX test2_idx
0 0 0 USE TEMP B-TREE FOR ORDER BY
create table test3 (x INTEGER);
sqlite> create index test3_idx on test3(x desc);
sqlite> explain query plan select x from test3 group by x order by x desc;
selectid order from detail
---------- ---------- ----------
-----------------------------------------------
0 0 0 SCAN TABLE test3 USING COVERING
INDEX test3_idx
0 0 0 USE TEMP B-TREE FOR ORDER BY
To double check:
sqlite> explain query plan select x from test3 order by x desc;
selectid order from detail
---------- ---------- ----------
-----------------------------------------------
0 0 0 SCAN TABLE test3 USING COVERING
INDEX test3_idx
Regards
Fabian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
