The storage engine for the table doesn't support repair. InnoDB or MyISAM?

Question

After repairing my database I received the following error:

scode_tracker.ap_visits note     : The storage engine for the table doesn't support repair scode_tracker.visit_length note     : The storage engine for the table doesn't support repair 

I found out that the type of table is InnoDB. The other table was MyISAM and it was repaired successfully.

After reading some topic here, the solution is to change it to MyISAM. I don't know much about InnoDB and MyISAM. I don't specify the type when I created the table. So my question is should I use MyISAM instead of InnoDB? If yes, how can I change it from InnoDB to MyISAM?

Answer 1

First is you have to understand the difference between MyISAM and InnoDB Engines. And this is clearly stated on this link. You can use this sql statement if you want to convert InnoDB to MyISAM:

 ALTER TABLE t1 ENGINE=MyISAM; 

Answer 2

InnoDB works slightly different that MyISAM and they both are viable options. You should use what you think it fits the project.

Some keypoints will be:

1. InnoDB does ACID-compliant transaction. http://en.wikipedia.org/wiki/ACID
2. InnoDB does Referential Integrity (foreign key relations) http://www.w3resource.com/sql/joins/joining-tables-through-referential-integrity.php
3. MyIsam does full text search, InnoDB doesn't
4. I have been told InnoDB is faster on executing writes but slower than MyISAM doing reads (I cannot back this up and could not find any article that analyses this, I do however have the guy that told me this in high regard), feel free to ignore this point or do your own research.
5. Default configuration does not work very well for InnoDB needs to be tweaked accordingly, run a tool like http://mysqltuner.pl/mysqltuner.pl to help you.

Notes:

• In my opinion the second point is probably the one were InnoDB has a huge advantage over MyISAM.
• Full text search not working with InnoDB is a bit of a pain, You can mix different storage engines but be careful when doing so.

Notes2: - I am reading this book "High performance MySQL", the author says "InnoDB loads data and creates indexes slower than MyISAM", this could also be a very important factor when deciding what to use.