force it to go the way you want by using "cross join" to force the ordering of
How would such "cross join" statemant look like? Am 30.05.2017 um 16:38 schrieb David Raymond:
It looks like it thinks that using the index on StyleId is going to be the most beneficial, as opposed to the rtree index. How it compares a normal index's stat1 to a virtual table's stat1 I don't know. In this case you can force it to go the way you want by using "cross join" to force the ordering of
the join.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Monday, May 29, 2017 9:28 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow Ok, here is a sample to try these queries: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip (825mb, 12 mio records) Before I change my app-logic to do the styleid-query on the app-side, I would like to know, if there might be a chance to get this fast on the sqlite-side. very fast, 77 records, 49ms: SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862 AND x0 < 30.46203 AND x1 > 30.00074766 AND 18 BETWEEN z0 AND z1 AND Pois_bb.Id = Pois.Id; Run Time: real 0.109 user 0.000000 sys 0.000000 selectid|order|from|detail 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?) fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??): SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219); Run Time: real 0.094 user 0.093601 sys 0.000000 selectid|order|from|detail 0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?) 0|0|0|EXECUTE LIST SUBQUERY 1 very slow: 55 records, 30000ms: SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862 AND x0 < 30.46203 AND x1 > 30.00074766 AND 18 BETWEEN z0 AND z1 AND Pois_bb.Id = Pois.Id AND styleid IN (9,48,73,200,142,31,219); Run Time: real 9.422 user 5.132433 sys 4.212027 selectid|order|from|detail 0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?) 0|0|0|EXECUTE LIST SUBQUERY 1 0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1: Forcing the order with CROSS JOIN SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND y1 > -15.12862 AND x0 < 30.46203 AND x1 > 30.00074766 AND 18 BETWEEN z0 AND z1 AND Pois_bb.Id = Pois.Id AND styleid IN (9,48,73,200,142,31,219); Run Time: real 0.078 user 0.000000 sys 0.000000 selectid|order|from|detail 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?) 0|0|0|EXECUTE LIST SUBQUERY 1 CREATE TABLE Pois( Id LONG PRIMARY KEY, Label VARCHAR(50), Info TEXT, Lat FLOAT, Lon FLOAT, Z FLOAT, Flags INT, StyleId INT ); CREATE INDEX Pois_StyleId ON Pois(StyleId); CREATE VIRTUAL TABLE Pois_bb USING rtree( Id LONG PRIMARY KEY, X0 FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT ); CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY, data BLOB); CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER); CREATE TABLE IF NOT EXISTS "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER); sqlite_stat1 tbl|idx|stat Pois|Pois_StyleId|11421177 16996 Pois|sqlite_autoindex_Pois_1|11421177 1 Pois_bb_rowid||11421177 Pois_bb_node||611106 Pois_bb_parent||611105 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-- /**************************************** ** Flemming Software Development CC ** Thomas Flemming ** PO Box 81244 ** Windhoek, Namibia ** http://www.quovadis-gps.com ** mail t...@qvgps.com ** +264 (0)81 3329923 ** +49 (0)6182 8492599 ***************************************/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
