This is what I have done. I took a zipcode table and my dealer table and made a view that pretty much joins the two on the zip code. Neither of these are indexed yet.
I then wrote the following two queries to find dealers. The second query can take almost 8 seconds to run and is the view of the dealer and zip code table. I'm not that comfortable with indexing my tables yet so I was looking for some advice on what to index in the tables. SELECT zip, lat, long, city, state FROM zips WHERE zip='#form.passedzipcode#' SELECT TOP 15 lat, long, name, city, state, zipcode, area_code, phone, 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) AS distance FROM dealers_geo #WHERE# 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) <= #form.passedradius# ORDER BY distance Phillip B. www.LoungeRoyale.com www.FillWorks.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Get the mailserver that powers this list at http://www.coolfusion.com
