On 21 Aug 2010, at 3:29am, Igor Tandetnik wrote: > ve3meo <holden_fam...@sympatico.ca> wrote: >> 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. > > It is not. It may very well take fewer instructions to implement a > straightforward linear scan than it is to join through the index.
Yeah. I think your formulation like this: > 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 ; is going to be the fastest. It involves more VM instructions but ends up searching fewer points. You might want to time it as it is, then add an index (just for testing) on just (position) and see whether that improves matters. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
