create index ddate_hits_a_idadvertiser_site_hostname_bench on a_idadvertiser_site_hostname_bench (ddate, hits);
--- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Iván de Prado > Sent: Monday, 17 June, 2013 05:30 > To: sqlite-users@sqlite.org > Subject: [sqlite] Simple Group By slowing queries by 6x > > I have query where adding a simple "group by" by date is slowing the query > too much, from my point of view. > > The following query: > > SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where > ddate > < '2013-08-01'; > > runs in 12 seconds. Approximately 423,327 rows scanned per second. 1 row > returned. The query plan is: > > SCAN TABLE a_idadvertiser_site_hostname_bench (~333333 rows) > > But adding the clause "group by ddate" slows too much the query: > > SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where > ddate > < '2013-08-01' group by ddate; > > runs in 67 seconds. Approximately 75,819 rows scanned per second. 63 rows > returned. > > The query plan is now: > > SCAN TABLE a_idadvertiser_site_hostname_bench (~333333 rows) > USE TEMP B-TREE FOR GROUP BY > > That means that this query is running almost 6 times slower than the one > without the group by. How could that be? The number of group by buckets is > very small (just 63 different dates), so it would be enough to do a scan > and keep a hash table in memory with partial results. > > Any ideas about what's the problem and how could we solve it? > > The Sqlite version we are using is 3.7.14.1. > > Regards! > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
