How to make server real-time calculations more efficient

Question

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.

Answer 1

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.

Answer 2

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.