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/[EMAIL PROTECTED]
