On Thu, Nov 10, 2011 at 12:28:24AM +0100, GB scratched on the wall: > Ok, just thought it worth mentioning. But a VACUUMed database may be > more efficient if you have multiple columns with multiple indexes > because you get a mixed sequence of data and index pages while > inserting data. VACUUM rearranges them so that pages belonging to an > object are grouped together which in turn helps drawing benefit from > prefetching and caching.
While that's true, the difference is more limited for indexes. VACUUM rebuilds tables in ROWID order, hence re-packing both records into a page, and pages into the database file. Indexes, on the other hand, are rebuilt with a table scan, essentially the same as CREATE INDEX on an existing table. This means that if the table rows (in ROWID order) are not already more or less in-order (according to the index) the rebuilt index will suffer from inserted values and re-balanced nodes. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
