MySQL scaling geolocation query

Question

So I have thousands of users with latitude and longitude. They check in with new coordinates every 30 seconds.

When they check in I need to send them the 100 people closest to them no matter how far away they are. In a crowded city this may be a radius of half mile. In the country it could take a radius of 100 miles to get 100 people.

It's easy enough to calculate the distance of each user from the user checking in and then do LIMIT 100. But that essentially does a table scan, calculates the distance between the checking in user and all other users in the table, sorts them by distance and then takes 100.

Won't be efficient at scale.

So what strategy can I use to scope the query to a subset of users and still get 100 results?

Answer 1

I don't think MySQL will be helpful for a longer duration. I'd recommend checking out the SingleStore database for your use case since it's efficient, scalable, and faster.

For your reference, Please go through the documentation by clicking the link here.