I've got the query working. The problem appears to be in the data on my local dev setup. All the data for the site (and others) comes into my local machine for processing and insertion into my database locally and is then transferred each day to my server.
I just created a new database locally from the server data and the query works fine. The weird thing is that all the data on the server is copied *from* the local database to the server database. I'll have to take a close look at the data. Thanks for the feedback! Rick -----Original Message----- From: Rick Faircloth [mailto:[email protected]] Sent: Thursday, December 30, 2010 9:42 PM To: cf-talk Subject: RE: Why does this sql cause MySQL 5 to hang? Interesting tool, Russ... thanks for the tip. Unfortunately, it didn't recognize my query as a "Select", so it couldn't provide any insight. Rick -----Original Message----- From: Russ Michaels [mailto:[email protected]] Sent: Thursday, December 30, 2010 8:35 PM To: cf-talk Subject: Re: Why does this sql cause MySQL 5 to hang? Rick, give this handy tool a try, it is rather good. http://www.jetprofiler.com/ <http://www.jetprofiler.com/>Russ On Fri, Dec 31, 2010 at 1:11 AM, Rick Faircloth <[email protected]>wrote: > > Hi, all and Happy New Year! (early...) > > I'm trying to run this query: > > select p.property_id, pl.propertyPhotoLink, o.office_name > > from properties p > > left join hmls_property_photo_links pl > on substring_index(p.mls_number, '_', 1) = pl.mlsNumber > and pl.photoLabel = 'Main View' > > left join offices o > on p.listing_office_mls_id = o.mls_office_id > > > order by #arguments.jsSort2# <--- this is "o.display_order_cbhr" > limit #itemsPerPage# <--- this is currently 20 > offset #mysqlOffset# <--- this is currently 0 > > But when I run it, MySQL hangs... > > If I remove the "order by" clause, it works. > > A fuller version of this query has been running fine, but when I added > the "limit" and "offset" to it, I started having problems with MySQL 5 > spiking the CPU to 50% and never completing the query. > > Based on my searching, the order of "order by", "limit, and "offset" > is correct. I've tried moving them around anyway, with no change. > > Thoughts on this? > > Thanks! > > Rick > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340332 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
