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 alas....what
concept am I missing?
SQLite version 3.7.2
sqlite> create table c(achr char,bchr char);
sqlite> create index c_chr on c(achr,bchr);
sqlite> explain query plan select achr,bchr from c where achr=bchr;
0|0|TABLE c
Why no use of the index in this case?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.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 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 no benefit in using an index on b for this query.
Change the query to:
select a.chr, b.chr
from b, a
where b.chr = a.chr and b.chr>0;
and the query plan becomes:
0|0|TABLE b WITH INDEX b_chr
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
>
Regarding
"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 ;"
surely the last 2 conditions are redundant assuming a.start<a.stop,
and b.start<b.stop
Regards,
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
