Justtechjobs.com Find a programming school near you






Online Campus Both


php-db | 2005011

RE: [PHP-DB] Slow Query From: Bastien Koert (bastien_k <email protected>)
Date: 01/13/05

Hi Wendell,

What you've proposed is not a bad solution. There is some initial work to
set up the stuff for the existing agents, but if you define a limit to the
range that the agent works in, then you can take the result stuff it into a
comma delimited string and place that into the agent_zip_codes field...then
the realtime time query is a simple IN (zip1, zip2, zipN) statement and
should be faster

Bastien

>From: "Wendell Frohwein" <wendell <email protected>>
>To: "'graeme'" <graeme_foster <email protected>>
>CC: <php-db <email protected>>
>Subject: RE: [PHP-DB] Slow Query
>Date: Thu, 13 Jan 2005 00:30:34 -0800
>
>Thank you Graeme.
>
>But unfortunately, there are a 700 + agents. This number keeps growing
>every day as well. I had a vision of another idea. I don't know how much
>time it will save. In the table that stores all the agents zipcodes that
>they cover, I added longitude and latitude to the table.
>
>So when my zip code radius function kicks in, it will find agents with
>there respective zip code.
>
>Before it would take the property zip, query the zipcodes table with
>over 42,000 zip codes, then put those in an array, then search for
>agents from there.
>
>The way I see it, I killed one step in the process. But at the time of
>me writing this letter I am still trying to get it to work. If I do, I
>will post up some numbers to see how much faster it is.
>
>But I am still looking for a better way to do it.
>
>Thanks once again to everyone.
>
>-Wendell Frohwein
>
>-----Original Message-----
>From: graeme [mailto:graeme_foster <email protected>]
>Sent: Thursday, January 13, 2005 12:02 AM
>To: Wendell Frohwein
>Cc: php-db <email protected>
>Subject: Re: [PHP-DB] Slow Query
>
>How many agents do you have?
>
>If the number of agents is small then you could set up a temp table
>which has a link to the agent, the client, and a calculation of the
>distance between the two. Then do your search on this temp table. Once
>you have the answer you want delete the records for this client. (that
>should allow for more than one client entering data at the same time,
>although you may want to properly think that scenario through)
>
>graeme.
>
>
>Wendell Frohwein wrote:
>
> >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
> >
> >
> >
>
>--
>Experience is a good teacher, but she sends in terrific bills.
>
>Minna Antrim
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php