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.



From: sqlite-users <> on behalf of 
Dominique Devienne <>
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 <> wrote:

> However, I’m still confused. Reading this
> 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

ROWID is the key of the B-tree I believe, so if you insert a row "in the
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 mailing list

Reply via email to