Re: Dealer/zip locator constipated

At 07:57 29-11-00 -0800, Tom Beidler wrote:

After being advised to pursue a "real" dealer locator from my last post (thank you!) I decided to revisit the original dealer locator I tried implementing that uses the US Postal zip/lat/long database. ~The Scenario~ I've built a us_zip database using the postal code/lat/long info from http://ftp.census.gov/geo/www/gazetteer/places.html, with 29470 records. I have a dealer database with 8200 dealers, their zips, and a boatload of other info, ie. shipping address, phone, email, etc. I've been trying to use this query; SELECT us_zip.zip, sqrt(power(69.1*(us_zip.latitude - 40.039078),2) + power(69.1*(us_zip.longitude - 76.486221)*cos(us_zip.latitude/57.3),2)) as dist, dealer.name, dealer.bill_zip FROM dealer LEFT JOIN us_zip on dealer.bill_zip = us_zip.zip WHERE sqrt(power(69.1*(us_zip.latitude - 40.039078),2) + power(69.1*(us_zip.longitude-76.486221)*cos(us_zip.latitude/57.3),2)) < 10 ~The Problem~ Whenever I run the query it takes too long to process, > 17 minutes. Just to make sure the query worked I duplicated the dealer database and removed all but 235 dealers and the query took 33 seconds (eternity!) to run. What other options do I have to expedite the query. Should I add to the WHERE to limit it to a range, say +- 1000 zips. I haven't indexed any of the columns involved in the query, would that help?

Oh yes that will help try making a index of all the fields you use in the query (and in that orther) i mean index name (field1, field2) Give the box enough memmory and set mysql_key_size (if you use mysql) huge I've build a search-engine with > 20.000.000 records wich get joined and all sort of stuff wich didn't gave me sleep for a week ;) Every query i do there is < 1 sec nice huh? :) Greetz, Wico

~The Plea~ I want to be good! I want to do the right thing but I'm lost. Any help would be greatly appreciated. Thanks in advance, Tom

.>>.>>>.>>>>>.>>>>>>>>>

Tom Beidler Orbit Tech Services 805.455.7119 (cell) 805.682.8972 (phone) 805.682.5833 (fax) tbeidler@mindspring.com

.>>.>>>.>>>>>.>>>>>>>>>

-- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net