Re: improving speed of mysql distance filter

I assume you don't want to calculate the distance from one point to every other point, becuase if you do, there is no way to avoid having the database calculate the distance to each point. Some distance formula optimizations would be possible but you can't avoid a calculation for each row.

However, if you want to find all points within say 1 mi, then you need to first filter the set of points by lat and lng. Below is an example

Slow:

SELECT DistanceFormula(fld.lat1,fld.lng1, lat2, lng2) AS Dist WHERE Dist < 1mi

Fast (Assuming lat and lng fields are indexed)

SELECT DistanceForumla(fld.lat1,fld.lng1, lat2, lng2) AS Dist WHERE fld.lat1 < lat2 + offset AND fld.lat1 > lat2 - offset AND fld.lng1 > lng2 - offset AND fld.lng1 < lng2 + offset AND Dist < 1 km

The offset should be a degree representation that is always more than 1 km. (e.g. (1mi * 360 / circumference of the earch) ) The second method allows the database to filter the set of points to a smaller set, and then calculate the distance from a point to a much smaller set.

Method 2 essentially draws a box around the start point, gets all the points inside the box, then calculates the distance from the start point to each point in the box. It is important to make sure the box is bigger than the filtering distance.

Hope this helps.