Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"? > That's joining two tables together. This is just because of the rtree, which is in Pois_bb (http://www.sqlite.org/rtree.html), has nothing to do with the second condition "styleid IN .." Am 30.05.2017 um 18:29 schrieb David Raymond: If you scroll down in my previous reply I put the explain query plan outputs in with the queries. Guess I should have mentioned that. (Re-copied them below) It was using the index on StyleId, thinking that was going to be faster. What Dr Hipp suggested in adding the unary + operator does is turn that into an expression rather than the raw field in the eyes of the planner, so it doesn't use that index on StyleId. "but the query is not in two tables. its two select-where in the same table." Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"? That's joining two tables together. The fields you wanted returned may only be from one of them, but it's still a join. The quick version(s) have Pois_bb as the outer loop and Pois as the inner loop. The slowed down version had Pois as the outer loop and Pois_bb as the inner loop. 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.00 sys 0.00 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=?) 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.00 sys 0.00 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 With the unary + operator 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); 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 ___ 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Thomas Flemming Tue, 30 May 2017 09:43:15 -0700 >> Try putting a "+" symbol before "styleid". Like this: >> >> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) > THATS IT !! :-))) > > 50ms with +, and 15000ms without the + > > How is that possible? Hello, best explained here: http://sqlite.org/optoverview.html#uplus (a most interesting document anyway) Regards, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
If you scroll down in my previous reply I put the explain query plan outputs in with the queries. Guess I should have mentioned that. (Re-copied them below) It was using the index on StyleId, thinking that was going to be faster. What Dr Hipp suggested in adding the unary + operator does is turn that into an expression rather than the raw field in the eyes of the planner, so it doesn't use that index on StyleId. "but the query is not in two tables. its two select-where in the same table." Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"? That's joining two tables together. The fields you wanted returned may only be from one of them, but it's still a join. The quick version(s) have Pois_bb as the outer loop and Pois as the inner loop. The slowed down version had Pois as the outer loop and Pois_bb as the inner loop. 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.00 sys 0.00 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=?) 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.00 sys 0.00 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 With the unary + operator 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); 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> Try putting a "+" symbol before "styleid". Like this: > > AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) THATS IT !! :-))) 50ms with +, and 15000ms without the + How is that possible? Am 30.05.2017 um 17:36 schrieb Richard Hipp: On 5/27/17, Thomas Flemming wrote: Hi, I have a table Pois with points of interest (geogr. coordinate, label, styleid) where I do regional querys using a rtree-index: 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 Thats very fast, 50ms. The problem is, when I add a second condition to get certain poi-types only in the area: AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 Try putting a "+" symbol before "styleid". Like this: AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) The query becomes really slow, 800ms. There is of course also an index on styleid. I also realized, just this query: SELECT * FROM Pois WHERE styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 is also slow for the first call. The second call is fast. (Using SQLite Expert Professional 3.5 for testing). Any ideas, how to speed this up? Thanks Tom -- / ** 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 -- / ** 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
On 5/27/17, Thomas Flemming wrote: > Hi, > > I have a table Pois with points of interest (geogr. coordinate, label, > styleid) where I do regional querys using a rtree-index: > > 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 > Thats very fast, 50ms. > > The problem is, when I add a second condition to get certain poi-types only > in the area: > > AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 Try putting a "+" symbol before "styleid". Like this: AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) > > The query becomes really slow, 800ms. > There is of course also an index on styleid. > > I also realized, just this query: > > SELECT * FROM Pois WHERE styleid IN > 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 > > is also slow for the first call. The second call is fast. > > (Using SQLite Expert Professional 3.5 for testing). > > Any ideas, how to speed this up? > > Thanks > Tom > > > -- > / > ** 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
but the query is not in two tables. its two select-where in the same table. Am 30.05.2017 um 17:18 schrieb Hick Gunter: Just like any other join, but with tables in the desired order and the word CROSS added SELECT ... FROM CROSS JOIN ... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017 18:15 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow 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.00 sys 0.00 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.00 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, 3ms: 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.00 sys 0.00 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 849259
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Just like any other join, but with tables in the desired order and the word CROSS added SELECT ... FROM CROSS JOIN ... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017 18:15 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > 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.00 sys 0.00 > 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.00 > 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, 3ms: > 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.00 sys 0.00 > 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||6111
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
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.00 sys 0.00 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.00 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, 3ms: 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.00 sys 0.00 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
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.00 sys 0.00 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.00 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, 3ms: 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.00 sys 0.00 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
On May 30, 2017 10:07:45 AM EDT, Thomas Flemming wrote: >Style.Id doesn't need to be LONG, you're right. I changed it but it >doesn't >make a difference. >Pois.Id need to be LONG because the source for this column is really >containing 64-bit values Integers in SQLite are of variable size; if an 8-byte size is required, it will scale accordingly. Specifying LONG will not yield an integer affinity like specifying INTEGER would, and for primary keys is not as efficient because the column is not an alias for rowid if you specify LONG. In short, you should always use INTEGER PRIMARY KEY if the column is whole numbers, regardless of size. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Tuesday, May 30, 2017 8:08 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > > Do you know which SQLite version is being used by SQLite Expert > > Professional 3.5? > sqlite 3.10.0 > > I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same > slow. On that matter, if you place another SQLite dll in the installation folder with a new name, it becomes available in Tools->Options->SQLite Library as an alternate choice over the shipped version. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
INTEGER and LONG can both store 64 bits in SQlite. The difference is that "INTEGER PRIMARY KEY" makes the column an alias for the rowid, whereas "LONG PRIMARY KEY" defines a second, possibly redundant index. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017 16:08 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > Do you know which SQLite version is being used by SQLite Expert > > Professional 3.5? sqlite 3.10.0 I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same slow. Style.Id doesn't need to be LONG, you're right. I changed it but it doesn't make a difference. Pois.Id need to be LONG because the source for this column is really containing 64-bit values (osm-ids). Tom Am 30.05.2017 um 13:29 schrieb Wolfgang Enzinger: > Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming: > >> Ok, here is a sample to try these queries: >> >> http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip >> (825mb, 12 mio records) > > Just a few quick observations ... > > First, I would replace all column declarations like > > LONG PRIMARY KEY > > to > > INTEGER PRIMARY KEY > > This can make a huge difference AFAIK. > >> 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; >> >> >> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??): >> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219); >> >> >> very slow: 55 records, 3ms: >> 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); > > Do you know which SQLite version is being used by SQLite Expert > Professional 3.5? Just wondering because my quick tests on this reveal > different query plans, depending on the presence of a sqlite_stat4 > table (absent in your database) and the SQLite version (I ran some > quick tests using SQLite 3.13 and 3.18). I'll have to dig deeper into > this in the next days though. > > Wolfgang > > ___ > 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> Do you know which SQLite version is being used by SQLite Expert > Professional 3.5? sqlite 3.10.0 I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same slow. Style.Id doesn't need to be LONG, you're right. I changed it but it doesn't make a difference. Pois.Id need to be LONG because the source for this column is really containing 64-bit values (osm-ids). Tom Am 30.05.2017 um 13:29 schrieb Wolfgang Enzinger: Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming: Ok, here is a sample to try these queries: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip (825mb, 12 mio records) Just a few quick observations ... First, I would replace all column declarations like LONG PRIMARY KEY to INTEGER PRIMARY KEY This can make a huge difference AFAIK. 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; fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??): SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219); very slow: 55 records, 3ms: 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); Do you know which SQLite version is being used by SQLite Expert Professional 3.5? Just wondering because my quick tests on this reveal different query plans, depending on the presence of a sqlite_stat4 table (absent in your database) and the SQLite version (I ran some quick tests using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next days though. Wolfgang ___ 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming: > Ok, here is a sample to try these queries: > > http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip > (825mb, 12 mio records) Just a few quick observations ... First, I would replace all column declarations like LONG PRIMARY KEY to INTEGER PRIMARY KEY This can make a huge difference AFAIK. > 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; > > > fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??): > SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219); > > > very slow: 55 records, 3ms: > 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); Do you know which SQLite version is being used by SQLite Expert Professional 3.5? Just wondering because my quick tests on this reveal different query plans, depending on the presence of a sqlite_stat4 table (absent in your database) and the SQLite version (I ran some quick tests using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next days though. Wolfgang ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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; fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??): SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219); very slow: 55 records, 3ms: 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); Thanks, Tom Am 27.05.2017 um 12:04 schrieb Thomas Flemming: Hi, I have a table Pois with points of interest (geogr. coordinate, label, styleid) where I do regional querys using a rtree-index: 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 Thats very fast, 50ms. The problem is, when I add a second condition to get certain poi-types only in the area: AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 The query becomes really slow, 800ms. There is of course also an index on styleid. I also realized, just this query: SELECT * FROM Pois WHERE styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 is also slow for the first call. The second call is fast. (Using SQLite Expert Professional 3.5 for testing). Any ideas, how to speed this up? Thanks Tom -- / ** 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Morning, > Does ANALYZE gather statistical data about rtree virtual tables? I seem to ANALYZE doesn't help. I'm busy preparing and uploading a sample-db, then it might be easier to figure that out. Tom Am 28.05.2017 um 11:01 schrieb Wolfgang Enzinger: Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp: On 5/27/17, Thomas Flemming wrote: Hi, I have a table Pois with points of interest (geogr. coordinate, label, styleid) where I do regional querys using a rtree-index: 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 Thats very fast, 50ms. The problem is, when I add a second condition to get certain poi-types only in the area: AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 The query becomes really slow, 800ms. There is of course also an index on styleid. I also realized, just this query: SELECT * FROM Pois WHERE styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 is also slow for the first call. The second call is fast. (Using SQLite Expert Professional 3.5 for testing). Any ideas, how to speed this up? Have you tried running ANALYZE on your database? Does ANALYZE gather statistical data about rtree virtual tables? I seem to remember that this is not the case. I don't have an appropriate database at hand right now since I'm busy currently with a different project, but I seem to remember that I observed something similar: queries with an rtree table involved seem to *always* prefer the spatial index over any other index, even if the clipping embraces the complete extent of graphical data. This is unefficient especially when the "related table" (that is, another table that is linked to the rtree virtual table by an object ID) holds much more selective (and indexed) criteria. This is just out of the top of my head; anyway, if you don't hear back from Thomas, please let me know, and I'll gladly provide more details (not before Wednesday though). And thanks again for then LEFT JOIN VIEW optimization - sorry that it caused so much trouble ... I was completely unaware of the many pitfalls this topic involves. Wolfgang ___ 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
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp: > On 5/27/17, Thomas Flemming wrote: >> Hi, >> >> I have a table Pois with points of interest (geogr. coordinate, label, >> styleid) where I do regional querys using a rtree-index: >> >> 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 >> Thats very fast, 50ms. >> >> The problem is, when I add a second condition to get certain poi-types only >> in the area: >> >> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 >> >> The query becomes really slow, 800ms. >> There is of course also an index on styleid. >> >> I also realized, just this query: >> >> SELECT * FROM Pois WHERE styleid IN >> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 >> >> is also slow for the first call. The second call is fast. >> >> (Using SQLite Expert Professional 3.5 for testing). >> >> Any ideas, how to speed this up? > > Have you tried running ANALYZE on your database? Does ANALYZE gather statistical data about rtree virtual tables? I seem to remember that this is not the case. I don't have an appropriate database at hand right now since I'm busy currently with a different project, but I seem to remember that I observed something similar: queries with an rtree table involved seem to *always* prefer the spatial index over any other index, even if the clipping embraces the complete extent of graphical data. This is unefficient especially when the "related table" (that is, another table that is linked to the rtree virtual table by an object ID) holds much more selective (and indexed) criteria. This is just out of the top of my head; anyway, if you don't hear back from Thomas, please let me know, and I'll gladly provide more details (not before Wednesday though). And thanks again for then LEFT JOIN VIEW optimization - sorry that it caused so much trouble ... I was completely unaware of the many pitfalls this topic involves. Wolfgang ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
On 5/27/17, Thomas Flemming wrote: > Hi, > > I have a table Pois with points of interest (geogr. coordinate, label, > styleid) where I do regional querys using a rtree-index: > > 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 > Thats very fast, 50ms. > > The problem is, when I add a second condition to get certain poi-types only > in the area: > > AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 > > The query becomes really slow, 800ms. > There is of course also an index on styleid. > > I also realized, just this query: > > SELECT * FROM Pois WHERE styleid IN > 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 > > is also slow for the first call. The second call is fast. > > (Using SQLite Expert Professional 3.5 for testing). > > Any ideas, how to speed this up? Have you tried running ANALYZE on your database? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Please post the output of the following command from the command-line shell: .fullschema --indent To capture the output of the above command, you can preceed it by ".once out.txt": .once out.txt .fullschema --indent To emphasize, use the command-line shell "sqlite3.exe" which you can obtain from the https://sqlite.org/download.html page. This is not a 3rd-party tool like SQLite Expert Professional. On 5/27/17, Thomas Flemming wrote: > Hi, > > I have a table Pois with points of interest (geogr. coordinate, label, > styleid) where I do regional querys using a rtree-index: > > 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 > Thats very fast, 50ms. > > The problem is, when I add a second condition to get certain poi-types only > in the area: > > AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 > > The query becomes really slow, 800ms. > There is of course also an index on styleid. > > I also realized, just this query: > > SELECT * FROM Pois WHERE styleid IN > 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 > > is also slow for the first call. The second call is fast. > > (Using SQLite Expert Professional 3.5 for testing). > > Any ideas, how to speed this up? > > Thanks > Tom > > > -- > / > ** 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT WHERE with RTREE and second condition slow
Hi, I have a table Pois with points of interest (geogr. coordinate, label, styleid) where I do regional querys using a rtree-index: 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 Thats very fast, 50ms. The problem is, when I add a second condition to get certain poi-types only in the area: AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 The query becomes really slow, 800ms. There is of course also an index on styleid. I also realized, just this query: SELECT * FROM Pois WHERE styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762 is also slow for the first call. The second call is fast. (Using SQLite Expert Professional 3.5 for testing). Any ideas, how to speed this up? Thanks Tom -- / ** 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