You're right; creating the index after inserting is faster. Indeed, I tried that out. With a larger set of data the gap is even more dramatic.
More benchmarks, taken from the same data dumped from a 3.7GB file being loaded via sqlite.exe, 10,602,743 rows: Case 1: Just inserts: 20 min Case 2: Inserts first, index after: 2 hrs 30 min Case 3: Index first, inserts after: 16 hrs 20 min Case 4: Index first, inserts after, not in a transaction: 26 hrs 58 min
Yes, that's much like what I was expecting.
As you may have guessed from the table definition this data is from a USENET newsreader client. It is storing article header information coming in from multiple internet servers via multiple TCP connections run by multiple threads. The same header will come in multiple times from a particular server, and also from multiple servers. Part of the record being stored is how many servers it is on, so you have to search the table to find and update it while data is being inserted. Thus, not defining the index first is probably not a good idea.
When the database is storing live data, you do indeed want to have the indexes in place before the database goes live. My suggestion for having no indexes is only for those few times prior to the database going live that you want to pre-load it with a mass of data.
The truly relevant issue here is how many live records are being *generated* by the USENET newsreader clients, and how long it takes to insert them into a database with records relative to the generation time. For example, if at times of peak activity the clients are sending you data that would become 100 records every second, then you are fine if SQLite takes a lot less than a second to insert 100 records.
Data from a file is a rather different situation relative to live data.
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
