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?
Try this: select t.achr, t.bchr from (select distinct achr from c) as alphabet join c as t on (t.achr=alphabet.achr and t.bchr=alphabet.achr); Under some circumstances - achr only has a small number of distinct values, the number of rows where achr=bchr is a small percentage of all rows in the table - this may be significantly faster than full table scan. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
