MySQL Forums
Forum List  »  General

Optimised way to search over 2 milllion poi data in mysql
Posted by: newbie Shai
Date: August 30, 2014 10:00AM

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4

select *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians(
destination.latitude ) ) * cos( radians(
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians(
destination.latitude ) ) ) ) AS distance
-- table containing targets to compare distance
from destination
order by distance limit 1
;
The problem we find this taking too long. Some forum suggest to limit the search based on +1 and -1 the lat and long value. The problem now we need to provide minimal one poi. So any optimisation help on this or better way of doing this?

Options: ReplyQuote


Subject
Written By
Posted
Optimised way to search over 2 milllion poi data in mysql
August 30, 2014 10:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.