> I was trying to create an index on an integer column in a very large > table with over 400,000,000 rows on an Ubuntu ... I > increased the cache size to 200000000 but to no avail.
That's only 200M of cache and your table is much larger. Sqlite can't index/sort efficiently lare data on disc - you need to do this in memory, so the cache size must be large enough to store whole index. The problem is that if data in indexed column isn't sorted and you will try to use created index for example to SELECT ... ORDER BY, it will be again slow with very large index because the speed will be limited by random reads from disc (reads from index will be sequential but finding corresponding rows in the table won't be). It 'should speed up after all pages will be read into the cache (so again you will need big cache and preferably large page size). Also SSD disc will help. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
