Thanks Ryan and Dominique. The quote “You can think of an SQLite table as 
essentially a btree covering Index by itself with the Key being the Row_ID” 
makes things a lot clearer.



Thanks to everyone for their replies and patience.



Regards



Tom







________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Dominique Devienne <ddevie...@gmail.com>
Sent: Monday, January 8, 2018 10:56:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On Mon, Jan 8, 2018 at 11:39 AM, x <tam118...@hotmail.com> wrote:

> However, I’m still confused. Reading this https://sqlite.org/
> queryplanner.html suggests the table is stored in RowID order. So what
> happens if I insert a record into Tbl with a lower ID than the existing 2.4
> million Ids?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --DD
_______________________________________________
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

Reply via email to