Without an index, EXPLAIN returns the following number of virtual machine instructions for each query:
41 - original query: 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 ; 39 - original query re-ordered: select * from A as A1, A as A2 where A1.name=A2.name and A1.position != A2.position and abs(A1.position - A2.position) <= 10 ; 41 - Jim's query revamped to work: select * from A as A1, A as A2 where A1.name=A2.name and A1.position != A2.position and A2.position between A1.position - 10 and A1.position + 10 ; In all cases above, Table A2 is used with an automatic index. create index idxNamePos ON A(name,position); With this index present, the above queries all use it EXPLAIN reports this number of VM instructions, respectively: 32, 30, 36. If the number of VM instructions is a good inverse indicator of efficiency, then the second query with the index appears to be the most efficient. I'm sure somebody will point out if it is not. I didn't include Simon's suggestion as I did not know how to implement it. Tom "Jim Morris" <jmor...@bearriver.com> wrote in message news:4c6f0a83.4090...@bearriver.com... > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
