Re: [sqlite] Packing integer primary key with field bits

Cheers Ryan. From: R Smith<mailto:rsm...@rsweb.co.za> Sent: 11 August 2017 11:14 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Packing integer primary key with field bits On 2017/08/11 10:21 AM, x wrote: > I’m wishin

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/11 10:21 AM, x wrote: I’m wishing I had kept my mouth shut now  Never do that. We do see some questions asked ad-nauseum (or silly ones which could have been answered by the fleetest of Google searches) and some people probably find some irritation from it, but most of us don't

Re: [sqlite] Packing integer primary key with field bits

I’m wishing I had kept my mouth shut now  It’s starting to get a bit above my head as programming’s little more than a hobby for me. Although I’ve been using c++ builder for a long time I only got round to trying to learn c++ properly a couple of years back during a lengthy illness. I even

Re: [sqlite] Packing integer primary key with field bits

> On Aug 10, 2017, at 5:53 PM, Richard Hipp wrote: > > See https://www.sqlite.org/src/file/ext/misc/compress.c > Thanks, that would be a good template to start from. But I recommend using Snappy instead of ZLib, unless

Re: [sqlite] Packing integer primary key with field bits

On 8/10/17, Jens Alfke wrote: > > If I were going to use it, I’d implement a pair of SQL functions to > compress/uncompress blobs, See https://www.sqlite.org/src/file/ext/misc/compress.c -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] Packing integer primary key with field bits

> On Aug 10, 2017, at 9:36 AM, Nico Williams wrote: > > Then proper compression (e.g., with zlib) will be much better for you > than what you're trying to do. zlib isn’t fast enough for usage like this. It’s better to use something like Snappy, which was designed to be

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/10 5:54 PM, x wrote: Ryan, I know what you’re saying about the list of bytes but I’d like it confirmed that the indexes are not stored in a format similar to the data. Oh it's similar, just not the same. I looked it up: http://sqlite.org/fileformat.html Fascinating reading. Take

Re: [sqlite] Packing integer primary key with field bits

On Thu, 10 Aug 2017, Clemens Ladisch wrote: x wrote: I’m thinking about this more from the gain in speed rather than saving space. Database performance is usually limited by I/O, i.e., you gain speed by saving space. To be clear, database performance is usually limited by the number of

Re: [sqlite] Packing integer primary key with field bits

On Thu, Aug 10, 2017 at 03:36:31PM +0200, Clemens Ladisch wrote: > x wrote: > > I’m thinking about this more from the gain in speed rather than saving > > space. > > Database performance is usually limited by I/O, i.e., you gain speed by > saving space. Then proper compression (e.g., with zlib)

Re: [sqlite] Packing integer primary key with field bits

Subject: Re: [sqlite] Packing integer primary key with field bits On 2017/08/10 4:51 PM, x wrote: > Valid point on the intended range Gunter. I don’t know enough about sqlite to > fully understand your index cell paragraph. I thought the way sqlite worked > was e.g. to get the value of

Re: [sqlite] Packing integer primary key with field bits

017 16:15 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Packing integer primary key with field bits Space savings will depend very much on what other data is in the table. If you have a 4096 byte page size and with an average record size o

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/10 4:51 PM, x wrote: Valid point on the intended range Gunter. I don’t know enough about sqlite to fully understand your index cell paragraph. I thought the way sqlite worked was e.g. to get the value of the 3rd column it had to read the lengths of col1 & col2 so it knows where

Re: [sqlite] Packing integer primary key with field bits

Space savings will depend very much on what other data is in the table. If you have a 4096 byte page size and with an average record size of 1000 bytes then saving 7 bytes for each of the 4 records wont free up enough space to fit a new record into that page. So savings in this scenario will

Re: [sqlite] Packing integer primary key with field bits

ts.sqlite.org> Subject: Re: [sqlite] Packing integer primary key with field bits The first problem with packing several integer fields into one is that you lose the capability of expressing NULL values in the packed fields without resorting to complicated and time consuming arithmetics modul

Re: [sqlite] Packing integer primary key with field bits

x wrote: > I’m thinking about this more from the gain in speed rather than saving space. Database performance is usually limited by I/O, i.e., you gain speed by saving space. > I have done my homework on this So what are the results of your measurements? Regards, Clemens

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/10 1:19 PM, x wrote: Thanks for the replies. I’m not sure I agree with Gunter and Ryan though. I’m thinking about this more from the gain in speed rather than saving space. To clarify, I’m suggesting replacing a compound key (made up of several integer cols) with an integer primary

Re: [sqlite] Packing integer primary key with field bits

onnerstag, 10. August 2017 13:19 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Packing integer primary key with field bits Thanks for the replies. I’m not sure I agree with Gunter and Ryan though. I’m thinking about this more from the gain in speed rather than

Re: [sqlite] Packing integer primary key with field bits

Saving a minimum of 7 bytes I meant. From: x<mailto:tam118...@hotmail.com> Sent: 10 August 2017 12:19 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Packing integer primary key with field bits Thanks for the replies. I’m not sure I agree

Re: [sqlite] Packing integer primary key with field bits

a minimum of 7 bits? From: Hick Gunter<mailto:h...@scigames.at> Sent: 10 August 2017 10:53 To: 'SQLite mailing list'<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Packing integer primary key with field bits For the sake of the argument, let's try to devise a wor

Re: [sqlite] Packing integer primary key with field bits

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

Re: [sqlite] Packing integer primary key with field bits

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 wrote: > For the sake of the

Re: [sqlite] Packing integer primary key with field bits

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

Re: [sqlite] Packing integer primary key with field bits

The "anyone" that does this already is called "SQLite". It doesn't amalgamate the INT fields as such, but it stores INTEGER values in only as much bits as is needed, so it achieves the goal of being more memory-efficient. So a 0, 1 or 2 takes up much less space than say a 786587626 or other

Re: [sqlite] Packing integer primary key with field bits

Simple answer: Don't! This sounds like a misguided attempt to save space in the disk image by messing around with the rowid of a table (which is what "integer primary key" declares the column to be an alias of). Whatever you stuff in there needs to be unique and, if you intend to use foreign