Actually looking at the subject title it looks he was.
Sorry.
RBS
On Thu, Aug 10, 2017 at 11:43 AM, Bart Smissaert <bart.smissa...@gmail.com>
wrote:
> 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
