On Mon, Oct 21, 2013 at 5:27 AM, Fabian Büttner <fabian.buett...@gmx.org>wrote:
> Hi, > > I have been thinking about a question on stackoverflow ( > http://stackoverflow.com/**questions/19236363/select-** > distinct-faster-than-group-by<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? > Not intentional. SQLite simply fails to recognize that by using the GROUP BY in descending order it could avoid the ORDER BY clause. This is an optimization that we have never considered because it has never come up before. > > 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<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
