Search code examples
mysqldistancespatial-queryspatial-index

MySQL nearest query

I have a MySQL table with indexed spatial data that looks like:

CREATE TABLE `mytable` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `TIME` datetime DEFAULT NULL,
  `SPOT` point NOT NULL,
  PRIMARY KEY (`ID`),
  SPATIAL KEY `SPOT` (`SPOT`)
) ENGINE=MyISAM AUTO_INCREMENT=6473924464345089 DEFAULT CHARSET=utf8;

Rows are inserted like:

INSERT INTO mytable (time, spot) VALUES (now(), (GeomFromText('POINT(110.0 120.0)', 4326)));

I am doing a query to find the closest spots given a latlong

SELECT id, x(spot) as longitude, y(spot) as latitude 
    FROM mytable
    ORDER BY st_distance(GeomFromText('POINT(10.0 12.0)', 4326), spot) LIMIT 5;

but it is doing a Full Table Scan though 'spot' is indexed.

I would appreciate any help optimizing it!

Solution

  • It doesn't help that Spot is indexed, MySQL still have to calculate each st_distance from the point 10.0, 12.0 to be able to order it.

    If you wan't to do searches like this fast you should add a where condition to narrow the number of points that can might close enough, that could use the index.