Hi, thanks to everybody that answered. I tried your suggestions but there was no measurable improvement. Possibly this is the best what I can get out of Sqlite. However, I tried a similar query on a larger table using both SQlite and Postgresql (same machine, same table structure + indices, same data). Postrgresql took 25secs for this query while it took more than 1 hour with SQlite. Obviously there is room for improvement. If I manage to find out what makes the difference I will post it here.
Thanks again and best regards, Hilmar El 10/15/2010 4:44 PM, Igor Tandetnik escribió: > 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). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
