A newbee to MySQL myself I had a rather odd experience: A simple inner join of three research tables took severeal hours. I posted the problem on the forum.mysql.com and the following is the result of the post.
To give the conclusion first:
- Always create indexes for each key-variable in each and every MySQL table, no matter how small or big.
ALTER TABLE MyTable ADD INDEX(MyID)
where MyTable is a Table in the database and MyID is one of the key variables. - Never use brackets in consecutive joint statements! The right way is:
select * from Table_A A inner join Table_B B on A.id = B.id inner join Table_C on A.id = C.id;
Brackets force the creation of temporary tables and increase execution time
The first one is the most important. It reduced the time for a join from more then 10 hours to a couple of minutes.
Rforge 