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
