Thanks for clarifying that. I think all the data to be inserted in tables with an INTEGER PRIMARY KEY will be sorted on that key, but I will have to check as sometimes these tables can be big, say a few million rows.
RBS -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 27 March 2007 23:34 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Thanks for that. > So if I can then I should create the table with INTEGER PRIMARY KEY. > Is it right that this won't affect the speed of any subsequent inserts or > deletes? > That depends on the data. If you insert records in order of ascending integer primary key, then the inserts will be very fast. If you insert records where the integer primary key is randomized, inserts will be reasonably fast until the size of your table exceeds the size of your disk cache. Then each insert will need to do multiple reads and writes to disk as it tries to figure out where in your massive table is the right place to put the new record, and then make space for that new record. All this disk I/O will slow things down dramatically. Every table has an integer primary key whether you declare one or not. If you do not specify an integer primary key then one is created for you automatically named "ROWID" or "OID". If you do not specify a value for the integer primary key when inserting, a value is selected automatically. The value selected is one more than the largest existing integer primary key in that table. That means that if you do not specify integer primary keys, the keys choosen are in ascending order and inserts are very fast. -- D. Richard Hipp <[EMAIL PROTECTED]> ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
