From: "Wendell Frohwein" <wendell@xxxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Subject: Slow Query
Date: Wed, 12 Jan 2005 14:48:20 -0800
First of all I would like to thank anyone who lends a hand in this
matter. Here is what im working with. Redhat 9, PHP 5.0.2,
Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for
pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with
mysql, and mysqli capability.
This is how the script works:
The client submits a property for an appraisal.
Based on the appraisal type and property zip code, It searches the
database for agents / appraisers with the zip code in question (property
zip code) in there coverage area (zip codes they are willing to do
appraisal work for). So when it finds a match, It adds that order to
there potential order list. It then waits for the to accept or decline
the order. If accepted the order becomes there's and alerts all the
other agents / appraisers that this order has already been accepted. The
do the work, send it back job is done.
This works fine right now rather it be a single order placed by a
client, or a spreadsheet imported to the system with 2000 orders or
more. So now you're saying if it works, why are you here asking for
help? Well instead of searching agents with the exact zip code match, I
would like to search a radius of zip codes. The first search would be a
2 mile radius, the next search (incase the first did not return any
result) would be 5 mile radius.
I purchased some software (php / mysql sql files) that contains every
zipcode in the united states along with a longitude and latitude for
each zip code. So the zip code script provided with this software allows
you to 1) enter a zip code and miles in radius, it will then spit out
all the zip codes in that radius into an array. 2) you can give it 2 zip
codes and it will tell you the distance in miles between the 2 (give or
take).
So I rewrote my script to do the follow:
The client submits a property for an appraisal.
Based on the appraisal type and property zip code, It does a radius
search of 2 miles from the property zip. If no matches are found, it
does a 5 mile radius search. For every zip code returned by each radius
search, It searches the database for agents / appraisers with the zip
code in question (zip codes from radius search) in there coverage.
This is the most stressful part I am assuming. This works fine for 1 -
10 orders. But when I import 30 or more, the script pretty much hangs as
well as mysql. I have to kill mysql with signal 9, start mysql it up
again. Then all is back to normal. I would paste the code in here but it
is really long and complicated.
I was hoping on the based on the operation of the script, someone would
suggest a better and faster way to search zip code radius, while
matching the results to agents within the system.
If I am asking for way to much time from someone, I apologize. If
someone is really interested in helping me sort this out, I could send
you code samples to see the process.
-Wendell Frohwein
