Re: Is there a solution to this?

[Bogdan Nicolau]

Hello,
I've stumbled upon this problem today and after a few hours of searching, I found a solution. Sometimes, MySQL hangs when creating a tmp table because of it's data fetching and sorting algorithms. In my case, when doing a multiple join on 3 tables, the query would just freeze. The query worked perfectly till yesterday. After running optimize, repair, flush..., checking the source code, etc., nothing seemed to work, so I just run explain on the query. The result: when joining one of the tables, I saw 2 interesting things:
1. The type was ALL
2. The Extra field showd me an Using temporary clause
The point? Well, the table was not using any indexes, nor did it have a primary key and that's a big problem when issuing an order by, for example.
The solution: check if all your tables inside joins are using an index or primary key.
In my case, just removing the "order by" made everything ok and that's when it hit me - indexes are the key.
The cause of the problem: when migrating to a new version of mysql or running an import, sometimes, certain indexes or primary keys get lost (I think they are human errors, not MySQL ones).
P.S. I didn't have time to check all the posts in the thread, so if anyone already gave this solution, my bad :)