Query optimization didn't have anything to do with it. I was running getDistance(zip1,zip2) instead a WHERE IN clause. My getDistance() function, which was based on Russ' would actually do two selects based on the zip code arguments.
For example select prospect, name, address, city, state, zip from prospects where zip in ( select zipcode from zipcodes where getDistance(prospects.zip,'27502') > 25) Let's say there were 100,000 rows in the prospect table. My original solution would've caused the getDistance() function to be called 100,000 times, and each time it would run two select queries to get the latitude and longitude, and then calc the distance. Effectively running 200,000 little select queries. Indexes weren't going to help =) At any rate, I got everything working super fast and blogged all about it. Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269029 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
