I think that you have hit a situation where the sort and index build algorithm in Sqlite is a problem and gets limited by disk head seeking. Others know more about this situation but I don't think there is much you can do to improve the speed.
Lars Aronsson wrote: > I have a database table with 68 million rows and 4 columns, of > which 3 are integers and one is a short text string. I'm > now trying to create an index on one of the integer columns, that > I forgot to create before I populated the table. But it takes for > ever and doesn't seem to stop. > > I estimate that each row takes 20 bytes, so the 68 million rows > should be a little over a gigabyte. Together with some other > data, the database file is 3.1 gigabytes. You can download a > compressed version (638 MB using bzip2) from > > http://mirabell.runeberg.lysator.liu.se/sv-counts-20080219.db.bz2 > > The schema (767 bytes) is available at > > http://mirabell.runeberg.lysator.liu.se/sv-counts-20080219.schema > > The operation I'm trying is > > create index counts_2 on counts(name); > > I'm using SQLite 3.4.2, which is the standard package in Ubuntu > Linux 7.10. I'm running this on an otherwise idle Intel Core 2 > Duo CPU with 4 GB of RAM. The operation takes several hours and > still running. Performance graphs indicate lots of iowait, with > many thousand blocks being written to disk each second. But very > little CPU power is being used. Is that reasonable? > > Do I need to use PRAGMA cache_size? > > Is there a good way to find out what sqlite3 is doing? I know the > "explain" command lists the byte code, but can it predict how long > an operation will take? > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
