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 <[email protected]> on behalf of Dominique Devienne <[email protected]> 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 <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
