Re: Dealer/zip locator constipated

Hi Tom: > 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? The problem is you're doing the calculation on each record in the database. Indexes won't help if you use this procedure because the index only deals with pointing to the actual data, not the calculated data. The thing to do is do your calculations ahead of time. Do a query to find the lat/long of the zip code requested. Run some calculations on that to find an acceptable range of lat and long, then query on that. In this case, indexing the lat and long fields will help tremendously. So, a sample query would contain something like this: ... WHERE (lat between $LatLow and $LatHigh) AND (long between $LonLow and $LonHigh) ... Hope that helps, --Dan -- PHP scripts that make your job easier http://www.analysisandsolutions.com/code/ SQL Solution | Layout Solution | Form Solution T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y 4015 7 Ave, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409