CODEWITHSUNDEEP
mysql
Parixit
Parixit

Reputation: 83

Queries stuck in "copying to tmp table"

I am running some sample tests against mysql, and finding that there are a bunch of queries which are stuck in "copying to tmp tables". They remain stuck in the same state. They are usually aggregate queries and I can kill those queries. But how can I find out what is causing them to be stuck? I am using mysql 5.1.42 with the innodb plugin.

Upvotes: 3

Views: 3037

Answers (1)

gabe.
gabe.

Reputation: 499

Your best bet is to use explain on the query that is giving you that message. 

explain select count(*) from my_table left join my_other_table on id1 = id2 order by id1;

This will break the query down for you and show you which piece is causing the copy to tmp tables. From there you can tweak the query, or change the way you are actually querying the data. In some cases, a schema change may also be in order.

Upvotes: 1

Related Questions