Re: [sqlite] How to optimize a multi-condition query

On Mon, 18 Oct 2010 15:07:35 +0200, Hilmar Berger wrote: > 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

Re: [sqlite] How to optimize a multi-condition query

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,

Re: [sqlite] How to optimize a multi-condition query

Hilmar Berger wrote: > I used EXPLAIN QUERY PLAN on the query and it looks like it does not use > any index on b at all, only if I use hardcoded conditions like b > 0. > > It appears that the real problem is that SQlite does not use indices for > both tables For

Re: [sqlite] How to optimize a multi-condition query

Hilmar Berger 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

Re: [sqlite] How to optimize a multi-condition query

Black, Michael (IS) wrote: > I love simple examples like this can help people with understanding > things...so I tried this which I thought would do what Hilmar > wants...but alaswhat concept am I missing? > > SQLite version 3.7.2 > sqlite> create table c(achr

Re: [sqlite] How to optimize a multi-condition query

ent: Fri 10/15/2010 5:16 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query > > > > On 15 October 2010 10:43, Hilmar Berger <hilmar.ber...@integromics.com> wrote: >>  Thanks ! >> >> However, I tri

Re: [sqlite] How to optimize a multi-condition query

Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of luuk34 > Sent: Fri 10/15/2010 7:40 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query >

Re: [sqlite] How to optimize a multi-condition query

ior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of luuk34 Sent: Fri 10/15/2010 7:40 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query On 15-10

Re: [sqlite] How to optimize a multi-condition query

On 15-10-10 14:34, Black, Michael (IS) wrote: > I love simple examples like this can help people with understanding > things...so I tried this which I thought would do what Hilmar wants...but > alaswhat concept am I missing? > > SQLite version 3.7.2 > sqlite> create table c(achr char,bchr

Re: [sqlite] How to optimize a multi-condition query

qlite.org on behalf of Simon Davies Sent: Fri 10/15/2010 5:16 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query On 15 October 2010 10:43, Hilmar Berger <hilmar.ber...@integromics.com> wrote: > Thanks ! > > However, I

Re: [sqlite] How to optimize a multi-condition query

On 15 October 2010 10:43, Hilmar Berger wrote: >  Thanks ! > > However, I tried what you suggested and there was no change. > > I used EXPLAIN QUERY PLAN on the query and it looks like it does not use > any index on b at all, only if I use hardcoded conditions like

Re: [sqlite] How to optimize a multi-condition query

Thanks ! However, I tried what you suggested and there was no change. I used EXPLAIN QUERY PLAN on the query and it looks like it does not use any index on b at all, only if I use hardcoded conditions like b > 0. It appears that the real problem is that SQlite does not use indices for both

Re: [sqlite] How to optimize a multi-condition query

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

[sqlite] How to optimize a multi-condition query

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