I've got a bunch of records for business locations in a directory. About 4,500 actually.
I have a bit of a problem with speed at the moment, for 2 reasons. When the user loads the page they are asked to input their postcode.
When they do so the page will reload and fetch all the businesses, foreach loop around them and calculate the distance between the postcode entered and the postcode stored for the business. After the initial loop is completed (which includes a SQL query). The records are then sorted by the distance away from the user.
It results in a tremendously slow page load and is pretty unusable at the moment.
What would your suggestions be for speeding this up? Considering you cannot cache the results easily since each user's postcode will be different.
Live source - http://discoversolar.co.uk/directory/type/mcs-installer/
You should have to look for query optimization
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
http://www.techrepublic.com/article/three-easy-ways-to-optimize-your-mysql-queries/6137168
One way would be to minimize the dataset too loop over. Here in Sweden our postcodes are organized in a way that if the code is close to another code then they're close to each other geographically as well.
You can maybe select the 100 closest datapoints and do your thing on that.
Even if your algo is O(n²) then minimizing your set with 4 500 will issue a big "performance" hit (not technically, but the end user will get the page much faster).
I have a mapping demo It which uses geocoding for location and a database of uk staions(2,757 records). It is quite fast and can be adapted for postcodes.
You need to avoid the PHP forloop by moving distance calculations into the database query: 1. Ensure you have coordinates in the database for business locations. 2. Get the coordinates for the users postcode. 3. Use curvature of the earth formula as follows.
<?php
$postcode_latitude = x;
$postcode_longitude = y;
$sql = "
select
*,
6371.04 * acos(cos(pi()/2-radians(90-latitude)) * cos(pi()/2-radians(90-".$postcode_latitude.")) * cos(radians(longitude)-radians(".$postcode_longitude.")) + sin(pi()/2-radians(90-latitude)) * sin(pi()/2-radians(90-".$postcode_latitude."))) as distance
from
my_table
having
distance < 50
order by
distance
limit 0,10";
?>
This will work fine on your 4,500 business locations. From experience on sletoH.com, approach 100,000 DB records and the SQL will slow down. At this stage you will need to avoid calculating distance for all DB records by adding a SQL where clause.