MySQL Forums
Forum List  »  Performance

Re: Is there a solution to this?
Posted by: Bogdan Nicolau
Date: March 12, 2008 08:37AM

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 :)

Options: ReplyQuote

Written By
December 07, 2006 10:30AM
September 23, 2005 01:56AM
October 05, 2005 04:58AM
October 05, 2005 11:49PM
October 06, 2005 01:34AM
October 06, 2005 04:57PM
January 21, 2008 10:14AM
January 21, 2008 10:53AM
May 19, 2008 09:41AM
February 05, 2008 10:27AM
Re: Is there a solution to this?
March 12, 2008 08:37AM
November 14, 2008 12:31AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.