Here is the structure of the zipcodes table CREATE TABLE `zipcodes` ( `zipcode` mediumint(5) unsigned zerofill NOT NULL default '00000', `lon` varchar(8) NOT NULL default '', `lat` varchar(8) NOT NULL default '', PRIMARY KEY (`zipcode`), KEY `lon` (`lon`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; And here is an example of one of the inserts INSERT INTO `zipcodes` VALUES (00501, '73.0456', '40.8153'); I am retrieving this data from phpmyadmin 2.6.0-rc2 Thanks again. -Wendell Frohwein -----Original Message----- From: Bastien Koert [mailto:bastien_k@xxxxxxxxxxx] Sent: Wednesday, January 12, 2005 6:12 PM To: wendell@xxxxxxxxxxxx; php-db@xxxxxxxxxxxxx Subject: RE: Slow Query are there indeces on the tables columns? Also does your query use the IN (zip1, zip2, zipN) format or or do you query for each zip individually? The second is absolute killer on the db bastien >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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
