MYSQL Query Performance - Searching by Distance

问题:

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.

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.

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

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

回答1:

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

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.

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.