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, 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 > -- /**************************************** ** 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
