Optimizing MySQL Queries for Geolocation Searches Using Latitude and Longitude

In MySQL, retrieving data based on geographic proximity is a common task. When working with longitude and latitude values, it's essential to use efficient queries to avoid unexpected results.

Consider the following query that attempts to find rows within a 25-mile radius of a center latitude and longitude:

SELECT *,(((acos(sin(($lat*pi()/180)) * sin((`latitude`*pi()/180)) cos(($lat*pi()/180))
* cos((`latitude`*pi()/180)) * cos((($lon - `longitude`)*pi()/180))))*180/pi())*60*1.1515)
AS `distance` FROM `geo_locations` HAVING `distance` 
While this query generally works, it can produce inaccurate results for some rows, showing them much further away from the specified radius.
To improve accuracy, consider the following optimized query:
SELECT
    `id`,
    (
        6371 *
        acos(
            cos( radians( :lat ) ) *
            cos( radians( `lat` ) ) *
            cos(
                radians( `long` ) - radians( :long )
            )  
            sin(radians(:lat)) *
            sin(radians(`lat`))
        )
    ) `distance`
FROM
    `location`
HAVING
    `distance` 
In this query, the :lat and :long parameters represent the latitude and longitude of the center point, and :distance represents the radius in miles.
The key difference between the two queries is the use of 6371 as the conversion factor to convert radians to miles. This factor is more accurate than 3959, which is used in the original query. Additionally, the optimized query uses a stricter formula for calculating the distance based on latitude and longitude.
By implementing these optimizations, you can significantly improve the accuracy of your MySQL queries when performing geolocation searches using latitude and longitude values, ensuring that the results are always within the specified radius distance.