Matt, Thanks for your response. It's going to take a bit for me to get the actual SQL available. I don't yet have permission to show that off. In the mean time, I can tell you that there are 10 table in the query, with 4 being references to the same table. I've been able to make an improvement that greatly improves the speed... by over-joining some records. That is, I'm joining 3 tables such that A joins to B, B joins to C, and C joins to A, basically. That was enough to let mySQL sort out a better plan.
Interestingly though, the result of the query (and it appears to be proper) is 0 rows. I believe it's the intermediate row set that may be huge. But I've never seen mysql spit out a temporary file while processing. Again, I'd really like someone's reference to a tuning guide, if there's a superior one available. Thanks again, Kevin > -----Original Message----- > From: Matt W [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 04, 2003 1:37 PM > To: [EMAIL PROTECTED] > Subject: Re: Query hung up in "Copying to tmp table" > > > Hi Kevin, > > I imagine the query is examining/returning so many rows that > it's going to take a long time to create the needed temporary > table (how long have you it go?). To start with, show us the > EXPLAIN output for the problem SELECT, along with the SELECT. > Also the size of the involved tables (rows and MB). > > > Matt > > > ----- Original Message ----- > From: "Kevin Fries" > Sent: Wednesday, September 03, 2003 6:51 PM > Subject: Query hung up in "Copying to tmp table" > > > > I'm having a problem where a complex SELECT query begins eating up > lots > > of CPU and never returns. In "show processlist", it > > reports "Copying to tmp table". Other queries seem to get locked > > waiting for this query, as well. > > > > This is on version 3.23.36 of mySQL, and I'm considering > upgrading to > > the latest 3.23.57, to see if that improves the > performance. I can't > > find any matching references in the Changes web pages that indicate > > this, though. Can anyone verify this? > > > > Alternately, I'm trying to find ways to possibly improve the step of > > "copying to tmp table". According to my "show variables" > output, the > > "tmpdir" is /tmp, and /tmp has plenty of room (85 megs or so). But > > while the query is running, I don't even see a corresponding file > there. > > The CPU is spiking with top reporting mysql as eating 85% > CPU. So I > > then assume that the 'problem' is happening before the file is > created? > > Does that sound right? > > > > If someone has a better reference for tracking down the source of a > > problem like this, other than > > http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very > > appreciative. > > > > thanks, > > Kevin Fries > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
