Not sure the OP wanted to touch the rowid/integer primary key. I think he just was contemplating putting a number of integers in one single, normal integer column. Might be mistaken there.
RBS On Thu, Aug 10, 2017 at 10:53 AM, Hick Gunter <h...@scigames.at> wrote: > For the sake of the argument, let's try to devise a workable scheme for > such an undertaking: > > Lets assume you have a 32-bit "real rowid" and four 8-bit "value" fields. > > How to distribute these in the 64-bit rowid? > > Rrid low = MSB | v1 | v2 | v3 | v4 | r1r2r3r4 | LSB or > Rrid high = MSB | r1r2r3r4 | v1 | v2 | v3 | v4 | LSB? > > Let's try to select a row by real rowid: > > SELECT id FROM t WHERE id & 0xffff = 1234; > > There is no index on "id & 0xffff" so this translates into a full table > scan (even if limited to one row, it still requires on average 1/2 of the > rows to be read). Creating an index is no option because we are trying to > save space, remember? > > SELECT id FROM t WHERE id BETWEEN (1234<<32) and (1234<<32 + 0xffff); > > This only works with "real rowid high" ("real rowid low" requires 2^^32 > ranges to work) and the lookup is fast, but the endpoints should better be > calculated in the calling program. > > So for a reasonably fast lookup by "real rowid", it needs to occupy the > most significant bits. > > Trying to select by one the values, by extension of the arguments given, > will either be a nightmare, default to a full table scan or require a > covering index (which requires more disk space than the 4-12 bytes per > record we have "saved"). > > Additionally, if any of the statements returns more than one row for any > "real rowid" then your table is shot up beyond repair... > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von x > Gesendet: Donnerstag, 10. August 2017 09:45 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [sqlite] Packing integer primary key with field bits > > As in cramming numerous integer columns into a 64 bit integer. > > Does anyone do this? > > Is the speed gain worth the additional confusion of extracting the columns > from the key? > > How is it best accomplished (virtual table maybe)? > _______________________________________________ > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users