Just like any other join, but with tables in the desired order and the word CROSS added
SELECT ... FROM <tablea> CROSS JOIN <tableb> ... -----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.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 ___________________________________________ 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
