What is the best way to efficiently calculate which points are close to a given lat/long using MySQL?

Question

I'm trying to design a MySQL schema that can store a list of users with an associated latitude and longitude.

I would then, for a given user, like to build a query which can return the nearest 50 users to him/her and sort those users by distance (with the nearest being presented first).

Given that there may be many thousands of users in this table, what is the most efficient way to store and query this data?

Answer 1

Take a look at MySql's spatial indexing.

You can also use the Great Circle Distance, there is a good SO article on it here.

Answer 2

Try reading this article: Creating a Store Locator with PHP, MySQL & Google Maps That article shows a MySQL solution for the Haversine formula, which is the best way to compute distance given latitude and longitude.