Hello!
I am creating a distance filter for use in my classifieds site,
http://safarri.com
Users are able to enter their zip code, and then show ads near to them.
I am currently using the below sql to do this:
SELECT {fields}, SQRT( (69.1 * ('48.055621' - a.lat)) * (69.1 * ('48.055621' - a.lat)) + (53.0 *('-116.585400' - a.lon)) * (53.0 *('-116.585400' - a.lon))) as distance
FROM {tables}
WHERE {criteria} HAVING distance < '{distance}' ORDER BY distance ASC
I was thinking that it might be innefficient to do this. After all, a complex mathmatical filter must be applied to every row in the database to determine it if meets the criteria.
I am thinking that adding the below code to my sql query might increase its speed. I am thinking that mysql will first compare the indexed lat and lons to the "box", and then do the "heavy" mathmatics on the subset which matched its original "box" criteria. Will mysql do this?
WHERE a.lat > a.lat - '{X}' AND a.lat < a.lat + '{X}' a.lon > a.lon - '{X}' AND a.lon < a.lon + '{X}' {criteria}
Thanks for your suggestions!
P.S. How do i determine {X}? Is it (({distance} * 53.0) / 2)?