On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote:
> I have a table with over 1 million rows and 20+ columns all of which > are indexed, I reasonably regularly recreate the table with new data > and find that the indexing process takes about 30 minutes. Are there > any settings/tweaks that I can use to reduce the time required to > create the index? > > Paul, thought about it recently. I think there's something you could do if you're ready to change the scheme a little. The index creation involves reading and writing. The size of the index in your case for most of the fields is smaller than the data of the main table so we'll forget about writing, possible it works well using the sqlite and system cache. But your reading for every index should read the whole table and the full record (if it fits on a single page) even if the index needs only one field. This process of full table reading can be comparatively fast (if your base is not fragmented), slow (if it's heavily fragmented) and amazingly fast if your entire table luckily fitted in the os system cache when your first field was indexed. And the latter is possible if your text fields contribute much to the record size and you're ready to move it to a different table accessing later with a join. I don't know your exact numbers, but 1m records with 20 modest integers (using much few bits than 64) should take about 50MB-100MB and many modern software/hardware configurations will possibly read it just once accessing this data from the cache when your second and later indexes are created. Max Vlasov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
