MySQL查询计算两个坐标之间的距离但使用HAVING子句太慢

舒巴姆·贾

我有以下查询来计算两个坐标之间的距离。但是，我想选择nkm范围内的用户。比如说，我想选择 100 公里范围内的所有用户。因此，我必须在HAVING这里使用子句。但是，我看到执行速度太慢了。即使只有两条记录，它也会非常缓慢地返回结果。我想知道一百万用户记录（将来）会发生什么。因此，我正在寻求对我当前查询的优化，以提高运行效率和速度。

SELECT *, 
  111.1111 *
  DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.latitude)) 
  * COS(RADIANS(b.latitude)) 
  * COS(RADIANS(a.longitude) - RADIANS(b.longitude)) 
  + SIN(RADIANS(a.latitude))
  * SIN(RADIANS(b.latitude))))) AS distance_in_km 
FROM users AS a
JOIN users AS b ON a.id <> b.id
WHERE b.id != :user AND a.id = :user 
HAVING distance_in_km < :maxdist 
LIMIT 30

更新

正如 Rick James 所建议的那样，我删除了GROUP BY子句并将其替换为AND a.id = :userinWHERE子句。到目前为止，这返回的结果与GROUP BY.

戈登·利诺夫

基本的答案是你不能让你的查询更有效率。对于您的方法，您基本上需要计算所有用户对之间的距离，这很昂贵。

可能有一些技巧可以使用。首先，您可能不需要反向对，因此您可以替换a.id <> b.id为a.id < b.id. 这将减少一半的工作。

您可以使用where子句来预过滤行。例如，在地球表面的大部分地区，纬度或经度相距超过 2 度的点相距超过 100 公里。这并非无处不在。但这对你来说可能已经足够了。这允许你写：

where a.latitude between b.latitude - 2 and b.latitude + 2 and
      a.longitude between b.longitude - 2 and b.longitude + 2

如果您的用户分布广泛，这将节省大部分三角函数。

但是，真正的解决方案是使用 MySQL 的 GIS 扩展。可以在文档中开始了解这一点。

编辑：

SELECT *,
   111.1111 *
   DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.latitude))
         * COS(RADIANS(b.latitude))
         * COS(RADIANS(a.longitude) - RADIANS(b.longitude))
         + SIN(RADIANS(a.latitude))
         * SIN(RADIANS(b.latitude))))) AS distance_in_km
FROM users a JOIN
     users b
     ON a.id <> b.id
WHERE a.id = :user AND
      a.latitude between b.latitude - 2 and b.latitude + 2 and
      a.longitude between b.longitude - 2 and b.longitude + 2
HAVING distance_in_km < 100

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。