most efficient way to find distances between many lat/lng using MySQL5.5

Hi all, after some current advise to best suit my requirements please.

I have a set of users with lat/lng in a table which although small at present I expect to grow into the hundreds of thousands over the next few years so I want to get this right to start with.

Normal scenario, for each one within a certain distance (say 5 miles) I want to return all the information such as name, age and distance from the searcher (who's lat/lng Ill have upon log in as a session)

My host uses MySQL5.5 so it appears I cannot use some of the newer built in MySQL distance features that I guess would help so I'm after the most efficient way to return and calculate please.

I've read about other types such as point etc and I can easily transform the lat/lngs to also be in a point column if that is better.

Please remember at present code must work on MySQL5.5

Thanks,
Neil

SELECT 
usr_id, 
usr_first_name, 
usr_location_lat, 
usr_location_lng, 
( 3959 * acos( cos( radians(51.33942032) ) * cos( radians( users.usr_location_lat ) ) * cos( radians(users.usr_location_lng) - radians(0.54173702)) + sin(radians(51.33942032)) * sin( radians(users.usr_location_lat)))) AS distance
FROM users 
ORDER BY 
usr_date_registered DESC 

Open in new window

LVL 3
Neil ThompsonSenior Systems DeveloperAsked:
Who is Participating?
 
Martin MillerConnect With a Mentor CTOCommented:
Convert all floating point numbers AND calculations to integers; integer math is much faster to work with.
0
 
Vijaya KumarCommented:
MYSQL 5.5 supports the distance functions such as cos,sin,acos.

visit this [https://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html]

Your code should be

SELECT 
usr_id, 
usr_first_name, 
usr_location_lat, 
usr_location_lng, 
( 3959 * acos( cos( radians(51.33942032) ) * cos( radians( users.usr_location_lat ) ) * cos( radians(users.usr_location_lng) - radians(0.54173702)) + sin(radians(51.33942032)) * sin( radians(users.usr_location_lat)))) AS distance
FROM users 
having distance <= 5
ORDER BY 
usr_date_registered DESC 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.