If there is an index on (name, position) the a where like below might use it.
A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 ) On 8/20/2010 3:54 PM, Peng Yu wrote: > Hi, > > I have the following code to search for neighboring positions > (distance<=10). But it is slow for large data set. I'm wondering what > is the most efficient query for such a search. Note that I don't > create an index, as I'm not sure what index to create on table A. > > $ cat main.sql > #!/usr/bin/env bash > > rm -f main.db > sqlite3 main.db<<EOF > > create table A (name text, position integer); > insert into A values('a', 1); > insert into A values('a', 5); > insert into A values('a', 21); > insert into A values('b', 3); > insert into A values('b', 15); > insert into A values('b', 19); > > .mode column > .headers on > .echo on > select * from A as A1, A as A2 where A1.name=A2.name and > abs(A1.position - A2.position)<= 10 and A1.position != A2.position; > > EOF > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users