Re: [sqlite] calculated-value indexes are not covering?

So, am I correct in thinking that an index on expressions already has all the required data to answer e.g. a SELECT DISTINCT? If so, that could be an optimization? Can I request this optimization to be made? :) Thanks, Wout. On Thu, Aug 10, 2017, 7:47 AM Wout Mertens

Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

Thanks, got it. So the stmt will be reset/finalized automatically if it reaches to NO-MORE-ROWS or SQLITE_DONE, OR it can be explicitly reset/finalized during the execution. One more, I signed in 5 days ago but didn't receive the notification email and today I changed email address and

Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

Asked and answered 5 days ago: Both. You are stepping a "statement" to the "end" (that is, completion, naught more to do). Thus the statement is automatically reset and any implicit transaction is committed. If however you executed a statement like: SELECT * FROM ReallyBigTable; and

[sqlite] What's the level of B+-Tree ?

In the "SQLite File Format" document, the BTree layout is described, but now I want to know how to get the BTree level (which is the 'K' value mentioned in the Documentation)? Generally, one B+Tree segment contains K keys and (K+1) pointers to child segments. From the source code, I found

[sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

Question for the paragraph in http://www.sqlite.org/lang_transaction.html: "An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared

Re: [sqlite] an error in FTS5 document

Thanks. Now fixed. On 8/10/17, Hideaki Takahashi wrote: > Hello, > > I found an error in a code example in the FTS5 document. > https://sqlite.org/fts5.html#extending_fts5 > > sqlite3_bind_pointer(pStmt, (void*), "fts5_api_ptr"); > > sqlite3_bind_pointer needs a parameter

[sqlite] an error in FTS5 document

Hello, I found an error in a code example in the FTS5 document. https://sqlite.org/fts5.html#extending_fts5 sqlite3_bind_pointer(pStmt, (void*), "fts5_api_ptr"); sqlite3_bind_pointer needs a parameter index and a pointer to destructor too, so I think it should be

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] Doc typos

On 8/10/17, Drago, William @ CSG - NARDA-MITEQ wrote: > The word hold should be holds in the first sentence of section 3. The > Rollback Journal. In the 3rd sentence give should be given: > Fixed now. Thanks. -- D. Richard Hipp d...@sqlite.org

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] Tutorials, books, video about SQLite

I've used "The Definitive Guide to SQLite" and have been pretty happy with it. I've probably learned more from this list and StackOverflow though. On Thu, 10 Aug 2017 at 06:29 Lars Frederiksen wrote: > Thank you for all your advices concerning books about SQLite. I will

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

[sqlite] Doc typos

The word hold should be holds in the first sentence of section 3. The Rollback Journal. In the 3rd sentence give should be given: http://sqlite.org/fileformat.html -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 /

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

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. Do you not think the cost of unpacking the bytes would be insignificant? Another possibility that should be considered is that if the database is

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

Paul, The record size will be fairly small. Probably under 100 bytes. Agree with you on your reduced disk I/o point, particularly as the index size might be significantly smaller than the full blown index size. From: Paul Sanderson Sent: 10 August 2017

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

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 col 3 value starts (I’ve seen a

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

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 modulo 257. The next is that you lose the possibility of expressing values outside of the

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

Saving a minimum of 7 bytes I meant. From: x Sent: 10 August 2017 12:19 To: SQLite mailing list Subject: Re: [sqlite] Packing integer primary key with field bits Thanks for the replies. I’m not sure I agree with Gunter

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 saving space. To clarify, I’m suggesting replacing a compound key (made up of several integer cols) with an integer primary key (which sqlite will use rather

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] Mailinglist question

On Wed, 09 Aug 2017 15:48:34 -0600, "Keith Medcalf" wrote: > > There is a solution since about 1984 ... it is called NNTP (Usenet News). > Google Groups is basically Usenet News with a (so some people thing > -- but not I -- I detest so-called web-forums) purty front end to

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

[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