Thanks for checking in this patch, Dan. http://www.sqlite.org/cvstrac/chngview?cn=4538
I see an opportunity for another DISTINCT to GROUP BY optimization if the columns are out of order, but an index present. For example, given: CREATE TABLE t2(x, y); CREATE INDEX t2_xy on t2(x, y); explain query plan select distinct x, y from t2; -- 0|0|TABLE t2 WITH INDEX t2_xy ORDER BY explain query plan select distinct y, x from t2; -- 0|0|TABLE t2 the last query could be transformed into: explain query plan select y, x from (select x, y from t2 group by x, y); -- 0|0|TABLE t2 WITH INDEX t2_xy ORDER BY Also, can you please consider checking in some form of my UNION optimization patch? It speeds up queries on compound selects and views by several times and uses just a fraction of the temp store: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg24859.html http://www.sqlite.org/cvstrac/tktview?tn=1924 Please ignore the constant folding discussion on the ticket which is not directly related to the UNION optimization. This is better handled in a separate patch. thanks. --- Joe Wilson <[EMAIL PROTECTED]> wrote: > Attached patch allows DISTINCT queries to take advantage of an appropriate > INDEX, if present. > > For example, given: > > CREATE TABLE foo(a, b); > CREATE INDEX foo_b on foo(b); > explain query plan select distinct b from foo; > > sqlite 3.5.2 returns: > > 0|0|TABLE foo > > and sqlite 3.5.2 + patch returns: > > 0|0|TABLE foo WITH INDEX foo_b ORDER BY > > The query "select distinct b from foo" is transformed into > "select b from foo group by b" by the patch. > > No regressions in "make test", although a few tests had to be > altered in order to take into account the new ordering of DISTINCT > rows. This is to be expected, as the order in which DISTINCT rows > are returned were never guaranteed without an explicit ORDER BY > clause. > > Please send any bug reports or comments to the list. > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > Index: src/select.c > =================================================================== > RCS file: /sqlite/sqlite/src/select.c,v > retrieving revision 1.359 > diff -u -3 -p -r1.359 select.c > --- src/select.c 31 Aug 2007 17:42:48 -0000 1.359 > +++ src/select.c 11 Nov 2007 23:49:01 -0000 > @@ -3067,6 +3067,14 @@ int sqlite3Select( > } > #endif > > + /* Optimizion: convert DISTINCT to GROUP BY to potentially > + ** take advantage of an INDEX > + */ > + if (p->isDistinct && !p->isAgg && !p->pGroupBy) { > + pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, p->pEList); > + isDistinct = p->isDistinct = 0; > + } > + > /* If there is an ORDER BY clause, then this sorting > ** index might end up being unused if the data can be > ** extracted in pre-sorted order. If that is the case, then the > Index: test/collate5.test > =================================================================== > RCS file: /sqlite/sqlite/test/collate5.test,v > retrieving revision 1.5 > diff -u -3 -p -r1.5 collate5.test > --- test/collate5.test 7 Sep 2005 22:48:16 -0000 1.5 > +++ test/collate5.test 11 Nov 2007 23:49:01 -0000 > @@ -57,17 +57,17 @@ do_test collate5-1.1 { > execsql { > SELECT DISTINCT a FROM collate5t1; > } > -} {a b n} > +} {A B N} > do_test collate5-1.2 { > execsql { > SELECT DISTINCT b FROM collate5t1; > } > -} {apple Apple banana {}} > +} {{} Apple apple banana} > do_test collate5-1.3 { > execsql { > SELECT DISTINCT a, b FROM collate5t1; > } > -} {a apple A Apple b banana n {}} > +} {A Apple a apple B banana N {}} > > # The remainder of this file tests compound SELECT statements. > # Omit it if the library is compiled such that they are omitted. > Index: test/insert4.test > =================================================================== > RCS file: /sqlite/sqlite/test/insert4.test,v > retrieving revision 1.8 > diff -u -3 -p -r1.8 insert4.test > --- test/insert4.test 9 Oct 2007 08:29:32 -0000 1.8 > +++ test/insert4.test 11 Nov 2007 23:49:01 -0000 > @@ -112,7 +112,7 @@ do_test insert4-2.4.1 { > INSERT INTO t3 SELECT DISTINCT * FROM t2; > SELECT * FROM t3; > } > -} {9 1 1 9} > +} {1 9 9 1} > xferopt_test insert4-2.4.2 0 > do_test insert4-2.4.3 { > catchsql { > Index: test/misc5.test > =================================================================== > RCS file: /sqlite/sqlite/test/misc5.test,v > retrieving revision 1.17 > diff -u -3 -p -r1.17 misc5.test > --- test/misc5.test 12 Sep 2007 17:01:45 -0000 1.17 > +++ test/misc5.test 11 Nov 2007 23:49:01 -0000 > @@ -490,6 +490,7 @@ ifcapable subquery { > LIMIT 10 > ) > WHERE artist <> '' > + ORDER BY 1 DESC > ) > ) > ) > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
