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:r...@michaels.me.uk] 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 <r...@whitestonemedia.com>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:340328 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
