Just Start Typing Text and Press Enter

Menu
Close
Aug 25, 2019

Convert MySQL Database Engine from MyISAM to InnoDB in PHPmyAdmin

0 Comment | By

This is a quick entry in my PHPmyAdmin series on how to use the power of PHPmyAdmin for optimization, repair, and table modification. Here we will begin by asking the questions. What is MyISAM, and what is InnoDB and why should I convert my tables to the new storage engine InnoDB?

What is MyISAM?

MyISAM or also known as ISAM storage engine was built for speed, compression, and optimization. MyISAM simply put us a storage engine that can be utilized by multiple operating systems and platforms alike. The over all size of a MyISAM table can be as large as 256 terabytes and can be compressed into read only tables to save space. On system startup MySQL will check MyISAM tables for corruption and will even go as far as to repair them if needed. Prior to MySQL 5.5, MyISAM was defaulted as the storage engine of MySQL, since then InnoDB is now the default storage engine.

What is InnoDB?

InnoDB again like MyISAM is a storage engine that fully supports ACID compliant transactions. It has been optimized for performance and supports roll-forward type operations, rollback, commit, and foreign keys. The size of a InnoDB table unlike its counter part MyISAM is 64 terabytes. Generally InnoDB cane be seen as a better option when having to deal with a large database because it supports transactions. However it can be said that MyISAM may be suited better for smaller databases.

Getting started:

Before getting started in converting our MyISAM tables to InnoDB it is very important that you create a backup of your existing MySQL database. This is a must and I would strongly advise it to avoid unexpected circumstances.

1.) Login in to PHPmyAdmin

2.) Once logged in select the database.

3.) Next proceed with clicking on the table that you wish to modify from MyISAM to InnoDB.

Select the database table

4.) Once the database table is selected proceed with clicking on the Operations Tab, then scroll down to Storage Engine and select InnoDB and then click on Go.

Modify Storage Engine

5.) Now you can verify your work by clicking on the database again like we did in step #1 and we can now see that our database engine type is set to InnoDB.

Verify in PHPMyAdmin InnoDB
Here we can verify if the table change that we made is set to InnoDB.

Conclusion:

In the future I will see about providing a command line (CLI) method to modifying the storage engine types but for now I felt with those who may not know or don’t have access to the CLI this blog entry would certainly help them out. If you have any questions just leave me a comment.

Leave A Comment

DON'T MISS ANY UPDATES