Any reason you can't add another field to your database? 0=equal
1=n1<n2 2=n1>n2 The create an index on that field. Then your query plan would look like this: sqlite> explain query plan select n1,n2 from table2 where flag = 1 intersect select n2,n1 from table2 where flag = 2; sele order from deta ---- ------------- ---- ---- 1 0 0 SEARCH TABLE table2 USING INDEX idxn4 (flag=?) (~10 rows) 2 0 0 SEARCH TABLE table2 USING INDEX idxn4 (flag=?) (~10 rows) 0 0 0 COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT) Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Rense Corten [rcor...@gmail.com] Sent: Thursday, June 23, 2011 1:20 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Query with UNION on large table Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a lot. As to RAM: I'm trying this on two different machines, one with 12 Gb and one with 32 Gb RAM. I won't be able to get more in the near future. Something that might be relevant is that the case of the 32Gb machine, the database is on an AFS. As for the ranges of n1 and n1: they are both roughly between 60000 and 12000000 . Here are the results of EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1 Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1; 1|0|0|SCAN TABLE table1 (~437976176 rows) 2|0|0|SCAN TABLE table1 (~437976176 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT) I'm not sure what "reasonable" should look like :). I'm running this query right now, let's see what happens. In my database, the mirrored pairs vastly outnumber the non-mirrored ones, to the extent that the non-mirrored pairs are actually extremely rare (but relevant). Generally, is there a way to figure out if sqlite is still doing anything, or whether it got somehow stuck? One thing I noticed is that it is not writing to the database while my problematic queries are running. Should it be? Rense On Thu, Jun 23, 2011 at 9:33 AM, Jan Hudec <b...@ucw.cz> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
