On Tue, April 19, 2005 8:55 am, Hank said: > Talk about over complicating things... here's the above query simplifed. > > I can not figure out why they were self joining the table three times: > > SELECT b.zip_code, b.state, > (3956 * (2 * ASIN(SQRT( > POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) + > COS(a.lat*0.017453293) * > COS(b.lat*0.017453293) * > POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2))))) AS distance > FROM zips a, zips b > WHERE > a.zip_code = '90210' > GROUP BY distance > having distance <= 5;
You'd have to time it, and *MAYBE* with enough indices this will all work out, but you'd probably be better off doing two queries. One to look up the long/lat for 90210, and another on just zips to calculate the distance. Benchmark on your own hardware and see for yourself. I could be 100% wrong. -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
