0

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
Rick James
  • 135,179
  • 13
  • 127
  • 222
Mark
  • 79
  • 1
  • 7

1 Answers1

0

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.

Davide Visentin
  • 735
  • 5
  • 19
  • Let's see `SHOW CREATE TABLE`. What version of MySQL? – Rick James Sep 01 '16 at 00:11
  • @RickJames The documentation above refers to mysql 5.7, so at least it works for all versions from that on (but I remember that I've see most of the features also in older versions). [there](https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html) there is a good example of create table, where you add also a spatial index. Simply substitute GEOMETRY with POINT. In that case the only column created is also the primary key; if you want to store duplicate points, you obviously have to add another column to use as primary key. – Davide Visentin Sep 01 '16 at 07:07
  • Oops, I probably meant my question for @Mark. – Rick James Sep 01 '16 at 18:33