On 6/22/10, Eric Smith <eas....@gmail.com> wrote: > Jim Wilcoxson wrote: ... >> Did you see my earlier note about combining your two integers into the >> primary key? This will also give you constant insert times, if you >> insert items in the order: ... > Thanks also for the tip on insertion order. Does that also hold for > multi-column indices (and not single-column indices transformed from two > integers)? I assume it's because we get more cache hits and fewer tree > rebalances when we insert in key-order?
Yes, I'm pretty sure this applies to multi-column indices too. So if you do your inserts as: a=0 b=0 a=1 b=0 ... a=0 b=1 a=1 b=1 Then the first set of rows with b=0 will be added in "more or less" constant time. When you start doing the second set of inserts, with b=1, that will cause pain, because you will be modifying every index record you created earlier and squeezing a new entry in between every existing entry. This will require a lot of journalling. I think it would run faster (less journalling) to insert in order with: a=0 b=0 a=0 b=1 a=1 b=0 a=1 b=1 etc. Even if you load the data without indexes and add the index later, my guess is that SQLite will still traverse the data in rowid order to create the index. So you are still better off inserting in the 2nd order rather than the first. The added advantage is that your index pages will be clustered together. Jim -- HashBackup: easy onsite and offsite Unix backup http://sites.google.com/site/hashbackup _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
