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.
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: firstname.lastname@example.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
> 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 mailing list