People being very specific about "integer primary key" is about another optimization by the way, and not because using "long" or "int" as a your data type is wrong in any way. Simply that "integer" primary key is needed for the optimization.
See: https://www.sqlite.org/lang_createtable.html#rowid -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of t...@qvgps.com Sent: Thursday, August 30, 2018 8:18 AM To: SQLite mailing list Subject: Re: [sqlite] Strategies to reduce page-loads? Thanks guys for quick and competent answers! After first sight, this "Z-Order curve" looks very promising, will give it a try. The LONG PRIMARY KEY is because I need a 64-bit integer (osm-id). I only learned now, that sqlite-int is also 64 bit long. Will change to INT PRIMARY KEY now. Tom ------ Originalnachricht ------ Von: "Richard Hipp" <d...@sqlite.org> An: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org> Gesendet: 30.08.2018 13:48:30 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/30/18, t...@qvgps.com <t...@qvgps.com> wrote: >> >>Structure is simple: >>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates >>BLOB, Flags INT, StyleId INT); >>And an rtree-index: >>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 >>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT); > >Three points that might help, either separately or in combination: > >(1) Use exactly "INTEGER PRIMARY KEY". "LONG PRIMARY KEY" and "INT >PRIMARY KEY" are not the same thing and do not work as well. > >(2) In the very latest versions of SQLite, 3.24,0 and the beta for >3.25.0, you can put the "Lines" information directly in the RTree: > > CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0, >z1, +Label, +Coordinates, +Flags, +StyleId); > >The extra columns in r-tree are prefaced by a "+" character so that >the r-tree module knows that they are auxiliary columns and not extra >coordinates. > >(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on >a "Morton code" or "Z-Order curve" of the coordinates. >(https://en.wikipedia.org/wiki/Z-order_curve) That will cause >features that are close together geographically to tend to be close >together within the file. There is are two extension functions in the >https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the >SQLite source tree that might help you with this. Or you can do the >same using your own functions. >-- >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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
