List: General Discussion « Previous MessageNext Message » From: Andy Eastham Date: January 10 2006 2:41pm Subject: RE: Geographical advice View as plain text
```James is right.  I use this method on a table with a combined index on 50
million rows and it's almost instantaneous.  Performance was vastly improved
after I did an
"alter table order by x"

Andy

> -----Original Message-----
> From: James Harvard [mailto:james.lists.tech@stripped]
> Sent: 10 January 2006 14:27
> To: Ben Clewett
> Cc: mysql@stripped
>
> If you visualise your search area as a circle around your 'target'
> coordinates, then you can eliminate many of the irrelevant rows by search
> for coordinates that fall within a square surrounding that circle.
>
> So, imagine a simple grid with target coordinates of 6,8 and a search
> radius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5
> AND 11.
>
> I'm not certain but I think MySQL should be able to used a combined index
> of (x,y) for that. As you probably know you can use EXPLAIN SELECT to
> check whether MySQL is using an index.
>
> HTH,
> James Harvard
>
> At 12:01 pm +0000 10/1/06, Ben Clewett wrote:
> >I have a need to locate (x,y) coordinates from mysql where they are close
> to another coordinate.  For instance, all pizza bars near my car.
> >
> >Example:  Searching for points closer than z to (i,j) using Pythagoras:
> >
> >SET i = 10;
> >SET j = 10;
> >SET z = 30;
> >SELECT x, y
> >  FROM coordinates
> >  WHERE POW(x - @i, 2) + POW(y - @i, 2) < POW(@z, 2)
> >
> >Big problem!  Must searches every row.  Linear indexing not able to help
> here.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

```
• Re: Geographical adviceJames Harvard10 Jan
• RE: Geographical adviceAndy Eastham10 Jan