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:304029
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
