The best bet is to index the field that your WHERE clause look in. So, if you do WHERE Zip='blah', then index by zip.
Create ZIP as your 'clustered' index if you are using SQL server. Then you can create as many non-clustered index's as you need, but as you create more index's, you will get diminished returns on performance increases. If you have a table with 10 fields and make 4 - 5 index's, it should work great. :) Have fun! Chris Peterson Gainey Transportation On Mon, 2003-09-08 at 10:42, Phillip B wrote: > 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:> This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
