Fabian Büttner wrote:
> 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'd guess just "missing optimization opportunity". I think this fragment of code
is responsible for that optimization: src/select.c, sqlite3Select():
=== cut ===
/* If there is both a GROUP BY and an ORDER BY clause and they are
** identical, then disable the ORDER BY clause since the GROUP BY
** will cause elements to come out in the correct order. This is
** an optimization - the correct answer should result regardless.
** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
** to disable this optimization for testing purposes.
*/
if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
&& OptimizationEnabled(db, SQLITE_GroupByOrder) ){
pOrderBy = 0;
}
=== cut ===
Adding DESC to pOrderBy "breaks" sqlite3ExprListCompare(,) (note: just changing
sqlite3ExprListCompare to ignore it would be insufficient and will likely result
in *breakage*).
> 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
