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, 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); 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
