Hi Rick, index will speed up the query if used correctly. I think you can put index on these tables smlc_acr_photos -> photo_mls_number smlc_com_photos -> photo_mls_number smlc_lot_photos -> photo_mls_number smlc_ren_photos -> photo_mls_number smlc_res_photos -> photo_mls_number
Also if the content of smlc_acr, smlc_com, smlc_lots, smlc_ren, smlc_res are different try using "union all" instead of "union" Rizal At 06:21 AM 4/23/2008, you wrote: >Hi, all... > >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. > >I've put together a "Browse Properties" page, which returns >20 records at a time and displays pagination. > >The query is pretty slow. > >Would indexes or perhaps another approach to this query >make it quicker? > >Here's the code that pulls together records from the tables >containing the various property types... > ><cfquery name="get_properties" datasource="#dsn#"> > > 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 > from smlc_acr sa > union > select sc.street_number as > prop_street_number, sc.street_name as prop_street_name, > sc.city as prop_city, > sc.public_remarks_01 as prop_public_remarks_01, > sc.public_remarks_02 as > prop_public_remarks_02, sc.list_price as prop_list_price, > (select scp.photo_filename from > smlc_com_photos scp where scp.photo_mls_number = >sc.mls_number limit 1) as prop_photo_filename > from smlc_com sc > union > select sl.street_number as > prop_street_number, sl.street_name as prop_street_name, > sl.city as prop_city, > sl.public_remarks_01 as prop_public_remarks_01, > sl.public_remarks_02 as > prop_public_remarks_02, sl.list_price as prop_list_price, > (select slp.photo_filename from > smlc_lot_photos slp where slp.photo_mls_number = >sl.mls_number limit 1) as prop_photo_filename > from smlc_lots sl > union > select sr.street_number as > prop_street_number, sr.street_name as prop_street_name, > sr.city as prop_city, > sr.public_remarks_01 as prop_public_remarks_01, > sr.public_remarks_02 as > prop_public_remarks_02, sr.list_price as prop_list_price, > (select srp.photo_filename from > smlc_ren_photos srp where srp.photo_mls_number = >sr.mls_number limit 1) as prop_photo_filename > from smlc_ren sr > union > select sres.street_number as > prop_street_number, sres.street_name as prop_street_name, > sres.city as prop_city, > sres.public_remarks_01 as prop_public_remarks_01, > sres.public_remarks_02 as > prop_public_remarks_02, sres.list_price as prop_list_price, > (select sresp.photo_filename from > smlc_res_photos sresp where sresp.photo_mls_number = >sres.mls_number limit 1) as prop_photo_filename > from smlc_res sres > ></cfquery> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:304032 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
