Requests stuck in "copy to tmp table",

Question

I run some test tests against mysql and find that there are many queries that get stuck in "copy to tmp tables". They remain in the same condition. These are usually aggregated queries, and I can kill those queries. But how can I find out what makes them stuck? I am using mysql 5.1.42 with the innodb plugin.

Answer 1

It is best to use a query explanation that gives you this message.

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

, tmp-. . .