Question:
On a medium (~2GB) MySQL database which is all MyISAM engine Performing the conversion to InnoDB what will be the impact on my system? Can I just convert via phpMyAdmin, for example?
Has anyone gone through this experience?
Answer:
Like everything else "It depends" Myisam is faster for linear queries and has very few checks on inserts and updates, making it faster for writing. Innodb is much more robust and guaranteed in terms of data consistency , like real working foreign keys
Evaluate
1st: need very high speed and don't have hard-to-recover references between tables? (yes: myisam +1, no: innodb +1)
2nd: the partition where the base is located on a RAID volume? (yes: negative impact of writing to innodb decreases, innodb +1)
3rd: Many concurrent writings in the same table or row? (yes: innodb +1, no: myisam +1)
there are many more factors to compute… but these I believe are the most impactful.
As for migration, it's easy, it's no use… you may (go!) have problems with a lack of consistency going from myisam to innodb, and you'll have to correct it by hand to ensure that everything was imported as it should…
obs: myisam is faster when the size of the tables is fixed… otherwise it can be smaller… (example use of varchar always)