Copying to tmp table

[flann]

When I run the following:

show processlist

I've been getting some entires of: Copying to tmp table

They don't hang like most of the people have when I do a search in google for the problem. What I want to know is, what does this mean? I just started noticing this and the load on the server seems to be higher than normal.

Thank you

[Deadeye]

were going to need more info then just what you provided.

What database are you running and what are you trying to do when it hangs?

Wesley

[zoul]

I believe, if I read your message correctly, that you stated "Its not hanging" ... and your simply asking "what is this" ...

Regardless of your DBMS, "copying to tmp table" processes usually mean that another query is 'staging' data ... for example, when performing join queries ... some DBMS's will place the results in a TEMP table prior to returning them ... or toss a large set of data into a TEMP table so that it can then query the larger set to retrieve a smaller set ... almost always having to deal with joins or sub-queries where more then one 'table' is being accessed ...

"Copying to tmp tables" is usually not something you need concern yourself with, however, if it's slowing you down... then perhaps you need to re-evaluate some of your queries and determine if they can be 'fine tuned' ... performance tuning usually weeds out a lot of unnecessary operations ...

[zoul]

as a follow up ... RE: 'the load on the server seems to be higher than normal.' ...

this is common, especially on databases that gradually grow ... at any rate of speed and size. The more data in the system, the slower it will run ... fairly common, performance tuning is again the best way to resolve this issue .. and sometimes, a query that performed really well with little data will perform horribly with lots of data ... the inverse is also sometimes true where a query that performs well with lots of data, performs poorly with little data (in comparison, heh)

[flann]

thanks for the responses. It is a Mysql 5 db. I'm not doing any subqueries in it.

I ran explain and it appears that it only does this when I use either group by or order by on the table. How would I optimize this better? I do have indexes on the fields being used in both the group by and order by for the table in question.

[zoul]

we'd need to see the table schema and the query your using to even begin to help with that ... and, as I was trying to say ... it's possible that there is no issue, and it's just a matter of 'lots of data' ...