Question

How to Retrieve Nearest Data from MySQL Using GPS coordinates

Answer

Location-based service is getting more popular these days. Hence, the most common query to database server is to find all data points that are within a given radius r from point (a, b). The problem is that to calculate the radius from a point, we'll end up with famous Pythagoras theorem.

While this is a simple concept, actually the squares and square roots are too complex for MySQL to make any useful optimization on which index to use. It ends up calculating the radius for all records in the entire table, which could cause performance issue.

So, how could we optimize the use of MySQL index in this case?

Let's draw a circle.

Let's draw a square outside the circle.

I believe now we can see how we could write a query which doesn't scan for all records in database. Here's the detail:

Initial query to find all data points within a square that has point (a,b) at its center is: