On 22 Jun 2011, at 6:25pm, Rense Corten wrote: > 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. I should mention that > table1 is not indexed, as indexing this large table also didn't work > out yet
I'm actually more concerned about your problem with creating the index than about your SELECT. In fact, it's possible that the SELECT optimiser inside SQLite has decided that the best way to perform your very complicated SELECT command is to make a temporary index. Please run "PRAGMA integrity_check" on your database as in <http://www.sqlite.org/pragma.html#pragma_integrity_check> With a billion rows, this will probably need at least an overnight run. In fact the integrity check, creating the index, and your SELECT will all need an overnight run. And I suspect that your are stressing some component of your setup, possibly your operating-system-level disk caching or your hard disk drive. The integrity_check should be a good first check to see whether it's worth pursuing the other things. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
