MySQL tables must have indexes


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:

  1. 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.

  2. 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
  3. The first one is the most important. It reduced the time for a join from more then 10 hours to a couple of minutes.

    Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s