On Wed, Jun 17, 2009 at 11:52:45AM +1000, John Machin scratched on the wall: > On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote: > > > One other note, if you have a primary key whose value is continually > > increasing your pk index can become imbalanced and therefore > > inefficient. > > A B-tree becomes imbalanced? How so? > > http://www.sqlite.org/fileformat.html#btree_structures says: "The tree > is always of uniform height, meaning the number of intermediate levels > between each leaf node page and the root page is the same." > > Do you have any evidence to the contrary?
It won't become imbalanced, but if you're inserting rows with an explicit INTEGER PRIMARY KEY value in a non-increasing order, the tree will require sorting and re-balancing. That takes time and requires additional disk writes (and, as others have pointed out, disk writes are VERY expensive due to their transactional nature). Also, depending on just how mixed up the pattern is, you can get into situations where a very large index will over-flow the default 1500 page cache-size. It is well known that if you want to build an index on a large table, increasing the cache size will help make that process faster. It might be true here as well. Try setting the page cache to something nice and huge, like 10x or 100x the default, and see if that helps. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
