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

Thanks Rick, it's much helpful for me. After some experiments on the internal dbstat table, I have such conclusions, much appreciate if you would review them: 1, My working Background: one WITHOUT ROWID table, this table's primary key is 30 bytes; dbstat shows there are at most 110 cells in

Re: [sqlite] transfer records with foreign key

On 2017/08/11 7:50 PM, Roman Fleysher wrote: Dear SQLiters, I have two tables linked by a foreign key, linkID. I need to transfer content of these two tables into two corresponding tables in another database preserving the link. However, the second database already has records and numeric

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

Dear Richard, Dear SQLiters, This is not clear to me as well. If I have two databases, db1 and db2. Both have table t. db1.t and bd2.t. I want to create a TEMPORARY trigger that upon insert in db1.t does something with db2.t. Because: TEMP triggers are not subject to the same-database rule.

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

On 8/11/17, Scott Robison wrote: > My understanding is that SQLite doesn't use the traditional definition of > b-tree because it doesn't use fixed size records/keys. It will cram as few > or as many as possible. Correct. More records crammed into one page means that

[sqlite] transfer records with foreign key

Dear SQLiters, I have two tables linked by a foreign key, linkID. I need to transfer content of these two tables into two corresponding tables in another database preserving the link. However, the second database already has records and numeric value of linkID can not be preserved. Nor its

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

On 11 Aug 2017, at 4:16pm, james ni wrote: > Yes, yes, that's what I'm seeking What is it that you’re ultimately trying to do with this information ? Are you doing research on the file format for forensic purposes ? Are you trying to edit the database file without

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

My understanding is that SQLite doesn't use the traditional definition of b-tree because it doesn't use fixed size records/keys. It will cram as few or as many as possible. I'm not in a position to confirm that, but it was something I read a few years ago I think. On Aug 11, 2017 9:16 AM, "james

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

Yes, yes, that's what I'm seeking From: sqlite-users on behalf of R Smith Sent: Friday, August 11, 2017 18:25 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] What's the level

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

That clears it up, many thanks! On Fri, Aug 11, 2017 at 2:32 PM Richard Hipp wrote: > On 8/11/17, Wout Mertens wrote: > > Aha ok, great! > > > > Now, forgive me, but there is still a difference in the byte code, and > I'm > > having a hard time

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

On 8/11/17, Wout Mertens wrote: > Aha ok, great! > > Now, forgive me, but there is still a difference in the byte code, and I'm > having a hard time decyphering it. The difference from the non-distinct and > the distinct is: > > * The table t1 is opened Yes, there is a

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

Aha ok, great! Now, forgive me, but there is still a difference in the byte code, and I'm having a hard time decyphering it. The difference from the non-distinct and the distinct is: * The table t1 is opened * There is an extra Seek in the scanning loop, it looks like it moves the read pointer

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

On 8/11/17, Wout Mertens wrote: > 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? :) > That

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

On 11 August 2017 11:08:02 GMT+01:00, james ni wrote: >Maybe we are talking the different thing. > > >Background of my problem: > >1, When one table grows larger, I found the INSERT speed is becoming >slower and slower; It seems to me that you may have chosen to view the

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

On 2017/08/11 11:08 AM, Clemens Ladisch wrote: james ni wrote: As in the example that I provided, there are 4 cells in a single btree page. So there must be some mechanism to determine hoe many keys that one cell can own. I want to know exactly the very value and just how to change the value

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

james ni wrote: > the INSERT speed is becoming slower and slower; > > the number of syscalls are increasing quickly; Insert the largest values last. Increase the cache size: . Decrease the amount of data stored in the index. (This is

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] What's the level of B+-Tree ?

Let's don't focus on the "file format" documentation, just focus on the BTree algorithm. I want to know the depth of the BTree, and how to reduce the depth of it. From: sqlite-users on behalf of james ni

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

Maybe we are talking the different thing. Background of my problem: 1, When one table grows larger, I found the INSERT speed is becoming slower and slower; 2, My task is to make it not so slower; 3, The linux perf tool shows the BTree depth is likely growing larger and larger after more

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

Jump to the byte offset specified by the "start of the cell content" header, which comes just after the number of pages (ie. offset 0x0f90 in your pasted example). Cross reference the data at that offset against section "2.1 Record Format" of the Database File Format page. By decoding the record

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

james ni wrote: > As in the example that I provided, there are 4 cells in a single btree > page. So there must be some mechanism to determine hoe many keys that > one cell can own. One key per cell: | Within an interior b-tree page, each key and the pointer to its | immediate left are combined

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

Thanks, but I'm more confused now. As in the example that I provided, there are 4 cells in a single btree page. So there must be some mechanism to determine hoe many keys that one cell can own. I want to know exactly the very value and just how to change the value to a larger one, for

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

The number of keys in an sqlite index page depends on the size of the database pages and the size of the (compressed) key value, which is stored in the same "row format" as the data. Child segment pointers are stored at the beginning of the page and key contents are stored at the end. The page

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] What's the level of B+-Tree ?

0001000: 0a00 040f 9000 0f90 0fc8 0fac 0fe4 So there are 4 cells in this btree page. But that's not what I'm seeking I want to know the level of the BTree, don't know if "level" is the exact term to express my idea, let's see in this: A cell in the BTree is

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

ni james wrote: > 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)? At the end of section 1.5, a "K" is defined. But I don't think that is the same K. Anyway, the