> Up till now, my db's have been relatively small > and fairly quick when being queried. I've never had to bother > with indexes or other methods of optimizing queries. > > However, I've now got a db of about 6400 real estate properties.
In addition to the indexing, have you considering just caching this query? Since you say it's typically much heavier on the read versus write side, that's an ideal situation to cache the data. You can certainly do this in the database, or easily in ColdFusion as well using the "cachedwithin" parameter. All you have to do is add some code that will refresh the cache after any writes (which is pretty easy to do just by calling the same query with the cachedwithin set to 0). With complex queries like this that are retrieving entire sets of data, the performance benefit simply from caching it can be quite significant, and certainly faster than using LIMIT. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304196 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
