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? -- Lars Aronsson ([EMAIL PROTECTED]) Aronsson Datateknik - http://aronsson.se _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
