geolocation-based querying

[boeing747fp]

is there a more efficient way of calculating/querying and sorting by "distance" based on latitude and longitude? this query works well but it is rather slow (0.9-1.7 seconds) and as the table grows (currently 150,000 rows), it slows down more.

PHP Code:

$query="SELECT a.id,a.gender,a.last_activity,a.photo_url,a.username,a.city,a.state,a.zipcode,a.country,a.lat,a.lon,b.status,
3956*2*ASIN(SQRT(POWER(SIN((".$mylat." - abs(a.lat))*pi()/180/2),2)+COS(".$mylat."*pi()/180)*COS(abs(a.lat)*pi()/180)*POWER(SIN((".$mylon."-a.lon)*pi()/180/2),2))) as distance
FROM `accounts` a, `profiles` b WHERE b.account_id=a.id AND a.zipcode != '' AND a.zipcode > 0 AND b.status != '##private##'
AND a.lat != '0.0000000000' AND a.lon != '0.0000000000' ORDER BY distance ASC,a.last_activity DESC LIMIT $limit"; 


[Stooshie]

Firstly, I would use the inbuilt mysql radians() function. It will be faster as it will be native.

Also, I would calculate the radian angles as return parameters first. e.g. RADIANS(mylat-lat) as latanglerad and then use that to calculate the distance parameter (again, prevents mysql calculating it twice).

Also, anywhere you are multiplying or dividing constants (pi/180 or 3956*2) calculate them manually and use that figure. Avoid having mysql do that calculation each time.

Hope that helps.