时间戳

首页 > 解决方案 > 通过空间索引优化附近位置 MYSQL 查询

mysql - 通过空间索引优化附近位置 MYSQL 查询

问题描述

我正在尝试优化我的 SQL 查询以显示最近的位置。

最初我使用的是以下查询

SELECT name,path, ( 6371 * acos( cos( radians(-36.848461) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(174.763336) ) + sin( radians(-36.848461) ) * sin( radians( latitude ) ) ) ) AS distance FROM cityDB HAVING distance < 200 AND path IS NOT NULL

此查询使用经度/纬度值来计算距离，需要 14.5 秒才能完成（太慢了！）

我创建了经度/纬度值的空间索引（点）以加快查询速度。

    SELECT DISTINCT name,
  (ST_Length(ST_LineStringFromWKB(
    LineString(
      pt,
      ST_PointFromText('POINT(174.763336 -36.848461)', 4326)))))
  AS distance
FROM CityDB
ORDER BY distance ASC LIMIT 99

然而，这比第一个查询平均要长 23 秒，这让我很惊讶！

我还应该对我的查询做些什么来加快它的速度吗？

我得到它低于 1 秒的唯一一件事就是添加这个（感谢这篇文章改善空间 MySQL 查询的性能）

    longitude BETWEEN longpoint - (50.0 / (111.045 * COS(RADIANS(latpoint)))) 
AND longpoint + (50.0 / (111.045 * COS(RADIANS(latpoint))))

但是，代码中有几个错误。如果我选择斐济，它只会显示<180 纬度的位置，如果我选择瓦努阿图，它只会选择<-180 纬度的位置。

    SELECT DISTINCT name, (ST_Length
(ST_LineStringFromWKB(LineString( pt, ST_PointFromText('POINT(-179.276277 -18.378639)', 4326))))) 
AS distance FROM CityDB 
WHERE longitude BETWEEN -179.276277 - (50.0 / (111.045*COS(RADIANS(-18.378639)))) AND -179.276277 + (50.0 / (111.045 * COS(RADIANS(-18.378639)))) 
GROUP BY (name) ORDER BY distance ASC LIMIT 99

除此之外，它还会显示几英里外的俄罗斯的位置（我知道我们可以为距离添加限制，但一定有比这更好的方法）

还有其他有效的方法来进行这样的查询吗？

标签： mysql

解决方案

推荐阅读