Thats interesting Max, I'll look at that. There are two main string fields one has an average of about 15 characters per field with a max of about 100. The other has an average length of about 150 characters with a max of about 250 (although occasionally up to 500 or so)
It may be possible to split the table into two with the main fields in one and all of the remainder (including the text fields) in the other. I'll have a play and see what I come up with On 25 November 2010 10:20, Max Vlasov <max.vla...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
