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

Reply via email to