Hello,
Here's my problem. I'm developing a product search script for a site that does the following:
1. User selects product from online catalog
2. User enters zip code and search radius
3. User is presented with all dealers within the search radius that carry the selected product.
Seemed pretty simple at first. The search IS working and it is accurate, but it is very slow (average 5 - 15 seconds per search).
Instead of posting the script, which right now is a mess from all the debug attempts, I'll present my logic.
1. Script selects longitude/latitude values from zip_code table.
SELECT Longitude, Latitude from zip_code WHERE Zip = user_zip
2. Script performs distance calculation on every record in zip_code database (about 35,000) and stores results <= search radius in an array.
3. Script then gets all records from product dealer database where dealer zip is within specified radius.
The environment this script is running in is Linux/Appache on a shared, quad-processor IBM server with 1 GB RAM.
Questions:
Would a JOIN work better for this scenario? My hesitation is, the distance calculation would then need to be in the SQL query which may slow things down even more.
Could the PHP interpreter be the culprit (not likely but maybe)?Would this be better handled by a compiled C++ CGI program or something?
Essentially, I'm looking for ANY educated ideas as to why the above scenario takes 5 to 15 seconds to return a page. Thank you for your help.
Here's my problem. I'm developing a product search script for a site that does the following:
1. User selects product from online catalog
2. User enters zip code and search radius
3. User is presented with all dealers within the search radius that carry the selected product.
Seemed pretty simple at first. The search IS working and it is accurate, but it is very slow (average 5 - 15 seconds per search).
Instead of posting the script, which right now is a mess from all the debug attempts, I'll present my logic.
1. Script selects longitude/latitude values from zip_code table.
SELECT Longitude, Latitude from zip_code WHERE Zip = user_zip
2. Script performs distance calculation on every record in zip_code database (about 35,000) and stores results <= search radius in an array.
3. Script then gets all records from product dealer database where dealer zip is within specified radius.
The environment this script is running in is Linux/Appache on a shared, quad-processor IBM server with 1 GB RAM.
Questions:
Would a JOIN work better for this scenario? My hesitation is, the distance calculation would then need to be in the SQL query which may slow things down even more.
Could the PHP interpreter be the culprit (not likely but maybe)?Would this be better handled by a compiled C++ CGI program or something?
Essentially, I'm looking for ANY educated ideas as to why the above scenario takes 5 to 15 seconds to return a page. Thank you for your help.