Thank you, I think my issue was that I didn't know HAVING could use a non-aggregate expression.
I'll do more reading tomorrow on that and bare columns in aggregate queries. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, May 11, 2017 4:01 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Version 3.19.0 about to enter testing On 05/12/2017 02:47 AM, David Raymond wrote: > My brain might just not be working right today. Would you be so kind as to > give an example for: > > "Transfer any terms of the HAVING clause that use only columns mentioned in > the GROUP BY clause over to the WHERE clause for faster processing." Say: CREATE TABLE t1(a,b); CREATE INDEX t1a ON t1(a); then: SELECT * FROM t1 GROUP BY t1.a HAVING t1.a=? may be rewritten as SELECT * FROM t1 WHERE t1.a=? GROUP BY t1.a In which the filtering is done before the grouping instead of after it, which will be faster. And because the filter uses the index, in this case it loads less data from disk too. Combined with SQLite's subquery flattening: https://www.sqlite.org/optoverview.html#flattening it can effectively transform things like: SELECT x,y FROM ( SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a ) WHERE x BETWEEN 8888 AND 9999; to SELECT x,y FROM ( SELECT a AS x, sum(b) AS y FROM t1 WHERE a BETWEEN 8888 AND 9999 GROUP BY a ); as well. Dan. > > ? > > Thanks > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Richard Hipp > Sent: Thursday, May 11, 2017 2:55 PM > To: sqlite-dev; General Discussion of SQLite Database > Subject: [sqlite] Version 3.19.0 about to enter testing > > SQLite version 3.19.0 will soon enter testing with a target release > date of 2017-05-25. If you have any issues or concerns, please bring > them up now. > > Change log: https://sqlite.org/draft/releaselog/3_19_0.html > Snapshot: https://sqlite.org/download.html > Checklist: https://sqlite.org/checklists/3190000/index > > The release will occur when the checklist goes all-green. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
