MYSQL查询性能-按距离搜索 [英] MYSQL Query Performance - Searching by Distance

问题描述

我有以下MYSQL查询，该查询在具有约50,000条记录的表上运行.该查询正在返回半径20英里以内的记录，而我使用where子句中的边界框来缩小记录范围.该查询将按距离排序，最多可有10条记录，因为它将用于分页页面.

I have the following MYSQL query which is running on a table with around 50,000 records. The query is returning records within a 20 mile radius and i'm using a bounding box in the where clause to narrow down the records. The query is sorted by distance and limited to 10 records as it will be used on a paginated page.

查询当前平均需要0.0210秒才能完成，但是由于网站非常繁忙，我正在寻找改善此问题的方法.

The query is currently taking 0.0210 seconds to complete on average, but because the website is so busy I am looking for ways to improve this.

adverts表中约有20列，并且在经度和纬度列上都有索引.

The adverts table has around 20 columns in it and has an index on the longitude and latitude columns.

有人能反正改善此查询的性能吗?我正在考虑创建一个单独的表，其中仅包含advert_id以及经度和纬度字段，但是想知道是否有人对下面的查询有其他建议或方法?

Can anyone see anyway to improve the performance of this query? I was thinking about creating a separate table which just has the advert_id and longitude and latitude fields, but was wondering if anyone had any other suggestions or ways to improve the query below?

SELECT  adverts.advert_id,
        round( sqrt( ( ( (adverts.latitude - '52.536320') *
           (adverts.latitude - '52.536320') ) * 69.1 * 69.1 ) +
           ( (adverts.longitude - '-2.063380') *
           (adverts. longitude - '-2.063380') * 53 * 53 ) ),
             1 ) as distance FROM  adverts
    WHERE  (adverts.latitude BETWEEN 52.2471737281 AND 52.8254662719)
      AND  (adverts.longitude BETWEEN -2.53875093307 AND -1.58800906693)
    having  (distance <= 20)
    ORDER BY  distance ASC
    LIMIT  10

推荐答案

您必须使用空间数据格式和空间索引:

You have to use spatial data formats and spatial indexes: how to use them.

特别是，您必须使用POINT数据格式在同一列中存储纬度和经度，然后在该列中添加空间索引.

In particular, you have to use the POINT data format to store both latitude and longitude in a single column, then you add a spatial index to that column.

空间索引通常被实现为R树(或导数)，因此搜索给定区域中所有点的成本是对数的.

The spatial index is usually implemented as an R-tree (or derivations) so that the cost of searching all points in a given area is logarithmic.

这篇关于MYSQL查询性能-按距离搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！