mysql 半径,在半径MySQL（纬度经度）内查找大表的最快方法是什么

Currently I have a few tables with 100k+ rows. I am trying to lookup the data like follows.

SELECT

*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude / 57.3), 2)) AS distance

FROM stops

HAVING distance < 5

ORDER BY distance limit 100

But currently this method slows with high load. Some queries are taking 20+ seconds to complete.

If anyone knows any better ways to optimize this would be great.

解决方案

Well first of all if you have a lot of geospatial data, you should be using mysql's geospatial extensions rather than calculations like this. You can then create spatial indexes that would speed up many queries and you don't have to write long drawn out queries like the one above.

Using a comparision with ST_Distance or creating a geometry with the radius of interest along with ST_within might give you good results and could be a lot faster than the current. However the best and fastest way to achieve this, ST_Dwithin isn't implemented yet in mysql.