MySQL Forums :: General :: Optimised way to search over 2 milllion poi data in mysql


Advanced Search

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 newbie Shai 08/30/2014 10:00AM
Re: Optimised way to search over 2 milllion poi data in mysql Peter Brawley 08/30/2014 11:28AM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/01/2014 11:42AM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/01/2014 08:04PM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/01/2014 09:40PM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/02/2014 12:11AM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/02/2014 02:11AM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/02/2014 07:22PM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/04/2014 04:48PM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/04/2014 08:58PM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/06/2014 10:18PM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/07/2014 12:25AM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/07/2014 04:09AM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/07/2014 10:40AM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/10/2014 02:36AM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/10/2014 03:42PM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/12/2014 04:41AM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/12/2014 11:20AM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/12/2014 02:21PM
Re: Optimised way to search over 2 milllion poi data in mysql newbie Shai 09/16/2014 10:15PM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 09/16/2014 11:34PM
Re: Optimised way to search over 2 milllion poi data in mysql Rick James 08/30/2014 09:45PM


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.