Reputation:
I am using a mySQL database on a site to calculate the distance of multiple points based on zipcodes, latitudes and longitudes. E.g. Point A at zip code 123456 has latitude 100 and longitude 200 Using a static formula, the distance between Point A and multiple other points are calculated and returned real time. The reference Point A may change to Point B with a different lat/long
The issue is that the server CPU is maxing out and taking quite a long time to do these calculations. I can increase CPU power, but wanted to know if there is more efficient way of doing this.
Putting the distances into a DB is not practical as it means a N x N lookup (999999 x 999999) and will increase exponentially as more datapoints are added.
Upvotes: 2
Views: 243
Reputation: 300837
A database server is not designed with fast arithmetic as a primary goal.
Make sure you are fully utilising indexes efficiently.
Use a fast square root formula if you need actual distances, otherwise just compare the squares of distances.
Use caching wherever it's appropriate.
Upvotes: 1
Reputation: 19488
As it sounds like you are doing geographic like things, have you considered http://postgis.refractions.net/ ? (if you are not bound to MySQL) as it is tuned/designed for calculations a bit like that.
Upvotes: 1