If I had set up the original db that's providing the data, it would have definitely been set up differently.
However, I'm pulling data from a data provider and inserting that in MySQL. I'm not sure how the tables could be set up much differently since, while they do have data in common, all the tables have quite a few differences because of the different property types. Unfortunately, the data vendor didn't use the mls number as a primary key for their tables (there's no keys specified, actually), so I just added a "property_id" field as an auto-incrementing primary key to each table when I transferred the data. Please advise if I'm missing something helpful! Anyway, as far as indexing the mls_number fields in the property tables (non-photo) tables... I did that and it did shave another 1-2 seconds off the time it takes for the results to appear, down from 3-4 seconds, so that was a good move! I've got to get this site live asap, so redesigning the db at this point isn't possible. The performance of the query for this part is good, so with the indexing, I don't think I'll have any performance problems with the search page, either. But again, if you've got any more ideas, let me know! Thanks for the help! Rick > -----Original Message----- > From: James Holmes [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 22, 2008 9:27 PM > To: CF-Talk > Subject: Re: Need some advice on speeding up query... > > Well, apart from a DB redesign so that you can do 1 query instead of a > union of 5, lets look at the first query as an example: > > select sa.street_number as prop_street_number, sa.street_name as > prop_street_name, > sa.city as prop_city, sa.public_remarks_01 as > prop_public_remarks_01, > sa.public_remarks_02 as prop_public_remarks_02, > sa.list_price as prop_list_price, > (select sap.photo_filename from smlc_acr_photos sap where > sap.photo_mls_number = > sa.mls_number limit 1) as prop_photo_filename > > Since you're joining smlc_acr_photos.photo_mls_number to > smlc_acr.mls_number, put an index on each column. Hopefully > smlc_acr.mls_number is a primary key and is already indexed. Repeat > for the other tables. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:304035 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
