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.
On Wed, Apr 23, 2008 at 7:21 AM, Rick Faircloth
<[EMAIL PROTECTED]> 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:304034
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
