MySQL nearest query

Question

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!

Andreas Wederbrand · Accepted Answer · 2014-12-20 20:24:12Z

1

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.

answered Dec 20, 2014 at 20:24

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gatti Over a year ago

Thanks a lot Andreas. It's interesting because I have another query that returns rows intersecting a rectangle, and there the index is used. I would have thought it would work in a similar way.

Collectives™ on Stack Overflow

MySQL nearest query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related