Yes, the tables are separated by property type, but no, they don't have the same fields. They're all different.
And, yes, these would be, by far, heavy on the read side and light on the write side. I guess I could create one massive table, encompassing all the various fields for all the property types. I'd have to check into that. Once I get this process of importing delimited text data into my MySQL db tables as temp tables, then importing that data into a permanent table with a auto-incrementing, primary key property_id field added *and* processing batches of photos by reading their filenames in the various directories, parsing the MSL number for each photo, and storing that in a corresponding photo table... Once all that is complete, as I've now done it all manually, I have to automate the process, because all the data and photos have to be updated daily. I pretty much worked that out, too. Scheduled tasks will take care of the automation. And once I get this all figured out and implemented for the Savannah MLS's data, I get to add in the Hinesville MLS's data... I should be getting paid more... Rick > -----Original Message----- > From: Jon Clausen [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 24, 2008 12:28 AM > To: CF-Talk > Subject: Re: Need some advice on speeding up query... > > Rick, > > Based on what I can see, it appears that the tables are separated by > property type (commercial, residential, lot, multiunit, etc.). You > would need to add a column in the table to identify that type, but it > seems to me - as long as they are identical in structure as it seems - > that merging them into a single table would make life easier in many > ways. > > Correct me if I'm wrong, but it would seem like property data would be > low-write/high-read tables so as long as you merge them you could > avoid the overhead of the UNION ALLs and then could index them > heavily(MLS#, Property Type & ZIP,etc.). Then, depending on your DBMS > type, you could create additional create views and caches at the DBMS > level to further optimize query speed. > > Writes are much slower on heavily large, heavily indexed tables since > the indexes have to be recreated or appended but I imagine, for this > type of data, that the agents could wait a tiny bit longer for their > entries if their records were retrieved exponentially faster. > > HTH, > > Jon > > On Apr 23, 2008, at 10:29 PM, Rick Faircloth wrote: > > > Now's here's a new problem I haven't encountered. > > > > How do I use a url variable to link to a property details page > > when the properties are in 8 different tables? The issue really isn't > > the variable, since I can use the aliased variable > > "property_id" (once I add it to the query). > > > > I know I could use > > property_details.cfm? > > property_id=<cfoutput>#get_properties.property_id#</cfoutput>, > > however, on the details page, I would need to search all eight > > property tables > > for the correct property id. > > > > Is this the method I'm stuck with because all the properties aren't > > in a single table? > > Pass the aliased variable "property_id" and search all 8 tables for > > it? > > > > Perhaps I should just put them all into one big property table > > instead of keeping them > > separate as the data vender has them... > > > > Thoughts? > > > > Thanks, > > > > Rick > > > > Here's the current state of my "union all" query with all the > > property tables finally in play: > > > > <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, > > (select so.office_name from smlc_off so where > > so.office_code = sa.listing_office) as > > prop_listing_office > > from smlc_acr sa > > union all > > 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, > > (select so.office_name from smlc_off so where > > so.office_code = sc.listing_office) as > > prop_listing_office > > from smlc_com sc > > union all > > 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, > > (select so.office_name from smlc_off so where > > so.office_code = sl.listing_office) as > > prop_listing_office > > from smlc_lots sl > > union all > > 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, > > (select so.office_name from smlc_off so where > > so.office_code = sr.listing_office) as > > prop_listing_office > > from smlc_ren sr > > union all > > 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, > > (select so.office_name from smlc_off so where > > so.office_code = sres.listing_office) as > > prop_listing_office > > from smlc_res sres > > union all > > select smul.street_number as prop_street_number, smul.street_name > > as prop_street_name, > > smul.city as prop_city, smul.public_remarks_01 as > > prop_public_remarks_01, > > smul.public_remarks_02 as prop_public_remarks_02, > > smul.list_price as prop_list_price, > > (select smulp.photo_filename from smlc_mul_photos smulp > > where smulp.photo_mls_number = > > smul.mls_number limit 1) as prop_photo_filename, > > (select so.office_name from smlc_off so where > > so.office_code = smul.listing_office) as > > prop_listing_office > > from smlc_mul smul > > > > </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:304129 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
