CODEWITHSUNDEEP
mysql
Ryan NZ
Ryan NZ

Reputation: 626

Optimize my nearby locations MYSQL query

I have a query which is causing my site to run very slow. Essentially it identifies the nearest location to a longitude / latitude point from a database of around 2m records.

Currently this query takes 7seconds to complete.

I have done the following to speed it up (before it was more than 15 seconds!)

Added index keys to name / longitude / latitude / path stored the path in the database so that it does not need to run Stored results into another table so we do not have to run the query again. Considered splitting the database by country, however this will cause a problem if the nearest location is in a neighboring country.

Any other ideas? Is there a way to possibly limit the longitude / latitude in the query eg + or - 2 degrees?

SELECT name,path, ( 6371 * acos( cos( radians(?) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(?) ) + sin( radians(?) ) * sin( radians( latitude ) ) ) ) AS distance FROM ".$GLOBALS['table']." HAVING distance < 200 AND path IS NOT NULL

Upvotes: 0

Views: 265

Answers (1)

Amadan
Amadan

Reputation: 198324

Do not use latitude and longitude columns, as this way indices are useless since you need to calculate the distance metric for each record every time you query, with no ability to optimise it.

MySQL now supports geospatial data using POINT datatype and CREATE SPATIAL INDEX, which MySQL knows how to optimise.

Something like this; though MySQL 8.0 should be even better.

Upvotes: 1

Related Questions