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
