MySQL Forums
Forum List  »  Performance

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

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

Options: ReplyQuote


Subject
Views
Written By
Posted
8282
December 07, 2006 10:30AM
9659
September 23, 2005 01:56AM
4920
October 05, 2005 04:58AM
6130
October 05, 2005 11:49PM
5389
October 06, 2005 01:34AM
6703
October 06, 2005 04:57PM
6528
January 21, 2008 10:14AM
5868
January 21, 2008 10:53AM
5328
May 19, 2008 09:41AM
5539
February 05, 2008 10:27AM
Re: Is there a solution to this?
6081
March 12, 2008 08:37AM
6140
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.