"RB Smissaert" <[EMAIL PROTECTED]> wrote: > > Looking up a record by INTEGER PRIMARY KEY is always twice as > > fast as looking up the same record by any other key > > Didn't realize that, but I have a question in connection with this. > It seems if you do inserts on a table it is faster if you have no INTEGER > PRIMARY KEY on that table and then later create an integer key on that same > field. But that would mean that selects and joins may be slower. > Am I seeing this right or is there any way round this, so have fast inserts > and still have the INTEGER PRIMARY KEY?
The b-trees used for tables are optimized for inserting new entries at the end of the table, because this is the common case. If you have an INTEGER PRIMARY KEY, then inserts will therefore be fastest if you insert in primary key order. If you do not have an INTEGER PRIMARY KEY, then SQLite makes one up for you automatically, and the made-up primary key (the rowid) is almost always larger than all previous rowids so the net effect is that you are always appending and always going quickly. I have not measured it, but I'm guessing that the speed reduction caused by inserting INTEGER PRIMARY KEY out of order is much less than the speed penalty of building a separate index. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
