Find locations in table that are within a certain radius distance of a given latitude/longitude We have a table named areas with column - id, name, lattitude, longitude and position. The position column is of POINT type in MySQL and has latitude, longitude value. 

To get areas within 25 kilometers of given latitude, longitude i.e. (28.638753, 77.073803) and order them based on proximity to this point, we are using the following query as recommended here by Google.

SELECT id, name, ( 6371 * acos( cos( radians(28.638753) ) * cos( radians( Lattitude ) ) * cos( radians( Longitude ) - radians(77.073803) ) + sin( radians(28.638753) ) * sin( radians( Lattitude ) ) ) ) AS distance 
FROM areas 
HAVING distance &lt; 25 
ORDER BY distance asc;


The table is:

CREATE TABLE `areas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Lattitude` decimal(18,6) DEFAULT NULL,
  `Longitude` decimal(18,6) DEFAULT NULL,
  `position` point NOT NULL,
  PRIMARY KEY (`ID`),
  SPATIAL KEY `sx_areas_position` (`position`)
)


The explain query gives following. Please note that rows = 21750 as it's test data. Actual data on production will be 100k+ rows.



Following is the index information:



This query is really slow when run with > 100k of records (takes up to 500 milliseconds). Is there any way to optimize it i.e.using spatial index?
There are lots of Q&amp;A here.  As a first cut, use a "bounding box".

ANALYZE TABLE and OPTIMIZE TABLE are irrelevant.

You mention Lattitude, but you don't have such a column.

Either go with point and SPATIAL, or go with latitude and longitude; it is probably folly to mix them.