Hi all,
I have a query that does not perform as fast as expected and would like
to know if anyone here has an idea on how to optimize it.
There are two tables, A and B. Both have fields chr, start, stop and
strand. A has about 50k entries, B about 12k.
Both contain intervals defined by start...stop. The intervals in B have
length 1, those in A any possible length.
I want to identify all intervals in B that fall into one of those in A
and get the corresponding interval data from A for each match of B.
Indexes has been created for all fields in A and B (e.g. create index
name on A(chr, start, stop, strand));
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 ;
This query takes about 130 seconds, but if when I remove the two last
conditions it drops to 0.5 seconds. Replacing both last conditions with
conditions like b.start > 10000000 or b.start <> 0 the execution time
will stay at 0.5 seconds.
Any help appreciated.
Thanks !
Hilmar
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
