MySQL commands execution time very long on a large table

Question

I currently use the following commands to compare long and lat values in two tables and update the sitecode column in one table based on the other like so:

CREATE FUNCTION great_circle_km (lon1 DOUBLE, lat1 DOUBLE, lon2 DOUBLE, lat2 DOUBLE)
RETURNS DOUBLE
RETURN 6371 * acos(cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)))


UPDATE `source` SET `source`.`sitecode` = (
    SELECT `sitecode` FROM `siteinfo`
    ORDER BY great_circle_km(`source`.`longitude`, `source`.`latitude`, `siteinfo`.`longitude`, `siteinfo`.`latitude`)
    LIMIT 1
    )

However, for a reasonably large table size of 8000 rows, the MySQL takes an upwards of 2 minutes to process and complete the UPDATE command. Is there any way i can optimise this?

Possible duplicate of [How can I optimize MySQL query for update?](https://stackoverflow.com/questions/9698895/how-can-i-optimize-mysql-query-for-update) — iamsankalp89, Sep 22 '17 at 05:47
Your, best bet might be to look into using spatial indices, q.v. [this highly upvoted question and answers](https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula). — Tim Biegeleisen, Sep 22 '17 at 05:47
@iamsankalp89 No, I don't think that is an appropriate duplicate link, because this problem is more about Haversine/great sphere and the challenges with that. — Tim Biegeleisen, Sep 22 '17 at 05:47
@TimBiegeleisen, Ok may be you are right. Please provide us best solution to help us — iamsankalp89, Sep 22 '17 at 05:48
why not calculate the result(as function great_circle_km does) in Code? — Wang Wen'an, Sep 22 '17 at 06:04

score 0 · Answer 1 · answered Sep 22 '17 at 09:46

Apologies if I've missed something here but that ORDER BY looks expensive, if both tables share a common key you could you try a MIN (or MAX) and benchmark it to see if query times improve? If there is no shared key, does siteinfo contain only one row?

UPDATE `source` A
SET A.`sitecode` = (SELECT MIN(great_circle_km(A.`longitude`, A.`latitude`, B.`longitude`, B.`latitude`)) FROM `siteinfo` B WHERE A.someID = B.someID);

Please let me know if there could be a better answer if more detail is available in the light of this suggestion,

Regards,

James

MySQL commands execution time very long on a large table

1 Answers1