I have a fairly lightly loaded MySQL server with a few tables that are updated every five minutes. Other than these updates, there are very few queries run against the database. The data is queried just a few times per month. Ever so often, one of the more complicated queries will result in the process getting hung in the "copying to tmp table" state. To be honest, the queries that get hung aren't even that complicated. Usually there's one or two joins, a GROUP BY, and an ORDER BY.

So far, when a process gets stuck in this state, I find that killing and restarting the process does not clear up the problem. I've changed my recovery method to killing the process, issuing a FLUSH TABLES, and restarting the process. This has worked fine for me, but I've heard of others that had to resort to restarting the daemon.