在半径MySQL(纬度经度)内查找大表的最快方法是什么

问题描述

目前，我有一些表，每行100k +.我正在尝试查找如下数据.

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

但是目前这种方法在高负载下会变慢.有些查询需要20秒钟以上才能完成.

如果有人知道有更好的优化方法，那就太好了.

解决方案

首先，如果您有很多地理空间数据，则应该使用mysql的地理空间扩展，而不是像这样的计算.然后，您可以创建空间索引最多可以查询很多查询，而不必像上面的查询那样编写冗长的查询.

与 ST_Distance 或创建具有感兴趣半径以及ST_within的几何图形可能会给您带来良好的效果，并且可能比当前速度快很多.但是，实现此目标的最佳和最快方法是 ST_Dwithin 尚未在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.

这篇关于在半径MySQL(纬度经度)内查找大表的最快方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！