> 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?
How do you think it should be used here? It's not that rows with the same values of achr and bchr stored together in the index - they are spread all over the place. And thus using index it will have to make full scan of it which is less efficient than full scan of the table. Or should I say it's more efficient than full scan of the table in very rare situations, so that I'm not sure if SQLite implements it at all. Pavel On Fri, Oct 15, 2010 at 8:34 AM, Black, Michael (IS) <michael.bla...@ngc.com> 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 > 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 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
