On 14 Oct 2010, at 5:46pm, Hilmar Berger wrote: > The query is as follows: > > select * from > a, b > where a.chr = b.chr and a.strand = b.strand and a.start <= b.start > and a.stop >= b.stop and b.start <= a.stop and a.start <= b.stop ;
> Indexes has been created for all fields in A and B (e.g. > create index name on A(chr, start, stop, strand)); I not certain from your phrasing, but this may not do what you think. It is different matter to do create index Achr on A (chr) create index Astart on A (start) create index Astop on A (stop) ... to what you did above. One creates one index on the sequence of four variables, the other creates four indexes each on one variable. However, to make this SELECT go fast, CREAT INDEX Bkey ON B (chr,strand) CREAT INDEX Bstart ON B (start) CREAT INDEX Bstop ON B (stop) May help. Also I recommend doing this: http://www.sqlite.org/lang_analyze.html Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
