Hilmar Berger <[email protected]> wrote:
> 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 ;
Drop those indexes you created, change the query to
select * from a, b
where a.chr = b.chr and a.strand = b.strand
and b.start between a.start and a.stop
and b.stop <= a.stop;
and create a single index on b(chr, strand, start) or b(chr, strand, start,
stop), listing fields in this exact order (adding stop to the index may or may
not result in marginal performance improvement - experiment).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
