-----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
