Hilmar Berger <hilmar.ber...@integromics.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users