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:[EMAIL PROTECTED] > Sent: 10 January 2006 14:27 > To: Ben Clewett > Cc: mysql@lists.mysql.com > Subject: Re: Geographical advice > > 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
