On 16 Jun 2010, at 5:33am, Jay A. Kreibich wrote: > On Tue, Jun 15, 2010 at 08:58:22PM -0700, Roger Binns scratched on the wall: >> On 06/15/2010 07:59 PM, Simon Slavin wrote: >>> The standard assumption about SQLite is that it's faster to do your INSERTs >>> first, then create the indices. >> >> If the index is created first then the data in the table and the pages >> making up the index will be interleaved. That is likely to lead to more and >> further seeks on accessing the index. Creating the index afterwards will >> result in a contiguous sequence of pages (assuming no existing free pages). > > Contiguous, yes, but the pages may not be in any logical order. > The internal node pages will get shuffled as the tree is built, > meaning you might still have a significant number of seeks.
Actually, I always figured that the CREATE INDEX routine was better tuned for creating many entries in an index at once, than writing them one by one. Each index has its own set of pages, right ? Imagine a table with three indices. Inserting a thousand rows would normally do something like mess with index 1; mess with index 2; mess with index 3; mess with index 1; mess with index 2 ... 1000 times There would be far less shuffling involved if all the index 1 work was done in one chunk, then all the index 2 work, etc.. Of course it's possible that SQLite is clever about transactions and if all the INSERT commands are in one transaction it does all the index-updating together. That would be neat. I haven't read the source code. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
