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
