MySQL Forums
Forum List  »  Optimizer & Parser

improving speed of mysql distance filter
Posted by: aubrey falconer
Date: May 19, 2006 05:27PM

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)?

Options: ReplyQuote


Subject
Views
Written By
Posted
improving speed of mysql distance filter
9533
May 19, 2006 05:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.