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 < 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?