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
