On Wed, Jun 22, 2011 at 10:25:52 -0700, Rense Corten wrote:
> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1<n2) GROUP BY
> n1,n2;
Have you tried explaining it (prefix the whole query with "explain query
plan" and run it)? Does the result look reasonable?
> This has the desired result on a small example, but when I try this on
> my actual table which has about 800 million rows, the query never
> seems to complete. It has been running for a couple of days now, and
> it doesn't seem sqlite is still doing anything (cpu usage dropped to
> almost zero), but I get no error messages.
Is the disk busy? It would mean you are trashing the caches, which is quite
likely. For this size of database, couple of gigabytes of cache would
probably be in order. Try giving it as much cache as possible given your
available memory using 'PRAGMA cache_size'.
--
Jan 'Bulb' Hudec <b...@ucw.cz>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
