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 tables, e.g.:

CREATE INDEX a_chr on a(chr)
CREATE INDEX b_chr on b(chr)

explain query plan
   select a.chr, b.chr
     from b, a
     where b.chr = a.chr;

Output:
0    0    TABLE b
1    1    TABLE a WITH INDEX a_chr


There is an example on how to use multiple indices on the same table 
here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
However, I'm not sure how to extend this to joined tables.

Thanks !

Best regards,
Hilmar

El 10/14/2010 7:12 PM, Simon Slavin escribió:
> 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 A and B (e.g.
>> create index name on A(chr, start, stop, strand));
> I not certain from your phrasing, but this may not do what you think.  It is 
> different matter to do
>
> create index Achr on A (chr)
> create index Astart on A (start)
> create index Astop on A (stop)
> ...
>
> to what you did above.  One creates one index on the sequence of four 
> variables, the other creates four indexes each on one variable.
>
> However, to make this SELECT go fast,
> CREAT INDEX Bkey ON B (chr,strand)
> CREAT INDEX Bstart ON B (start)
> CREAT INDEX Bstop ON B (stop)
>
> May help.
>
> Also I recommend doing this:
>
> http://www.sqlite.org/lang_analyze.html
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to