Joe Wilson <[EMAIL PROTECTED]> wrote: > Anyone have any ideas how to speed up GROUP BY on huge views > in recent versions of SQLite? > > http://www.sqlite.org/cvstrac/tktview?tn=1809 > > The older versions of SQLite (prior to SQLite 3.2.6) used to > perform GROUP BY operations in the main table loop, grouping > rows as it went along. But the new version appears to create > a huge temp table of results, sort them, and only then determine > the groups. This takes up a massive amount of temp store. > The old GROUP BY algorithm excels in the case where there are > a lot of groupable rows in the dataset; i.e., the typical case. >
The old algorithm required enough memory to hold the entire results set. The new algorithm uses bounded memory, which is a very important advantage if you have a GROUP BY with a large result set. The new algorithm will run much faster if you have an index on the GROUP BY term(s). -- D. Richard Hipp <[EMAIL PROTECTED]>
