Wow, Rizal... I had my doubts about how much putting indexes on just the photo_mls_number fields in the photo tables would help, but the speed is greatly improved!
I figured with the complexity of the query that just those indexes wouldn't make much difference, but they did! Thanks! Rick > -----Original Message----- > From: Rizal Firmansyah [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 22, 2008 9:18 PM > To: CF-Talk > Subject: Re: Need some advice on speeding up query... > > 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:304033 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
