Convert your MySQL database from MyISAM to InnoDB

MySQL database servers have two main storage engines, MyISAM and InnoDB. The storage engine is the management software used to create, read and update data in the database.

A website owner can choose what storage engine is used for each of the MySQL database tables on a website. For small websites, the storage engine doesn't make much difference. However, for larger/busier websites and e-commerce stores, you should discuss the best storage engine with your web developer or IT person.

Why is InnoDB preferred?

One major difference is that InnoDB implements row-level lock while MyISAM can do only a table-level lock.

A lock is a temporary mechanism to lock a database table/row so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time.

InnoDB has the advantage that it only locks a row, not the full table. This means it can process more SQL queries at any one time, which is important on a busy site with a lot of customers or orders. For example, think of Black Friday when an e-commerce website might receive hundreds of orders a minute. InnoDB will perform much better. MyISAM won't cope as well, there could be a bottleneck as orders cannot be entered into the database quickly enough.

Convert some or all database tables?

You can choose to convert all database tables or only convert the busiest/largest database tables (like a table containing order info). The choice is yours.

If you have a WordPress website running WooCommerce, you could convert those tables first.

If you have a ZenCart website, you could convert zen_orders and zen_whos_online. From ZenCart version 1.5.6 the default engine is InnoDB for all tables, but older versions will need manually converted.

Steps to convert to InnoDB using the cPanel hosting control panel

Any modifications to databases are risky, which is why we always recommend you to generate and download a backup of the database before applying any of the following steps.

• In cPanel control panel, open phpMyAdmin database manager
• In the left menu of phpMyAdmin, choose the applicable database. Take a note of the database name.
• Use the Export tab to download a copy of the database to your own computer. It's always important to take a backup before commencing any work.
• Once the backup has completed, go back to the Browse table to see a list of all the database tables.
• At the top of the list of database tables, you'll see an "+ Options" link. Click on it, and change 'partial texts' to 'full texts'.
• Go to the SQL tab and enter the following SQL command into the box. On the first line, replace name_of_your_db with the actual database name!

SET @DATABASE_NAME = 'name_of_your_db';

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND ENGINE = 'MyISAM'
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY table_name DESC;

• You should get results that look something like this:

• Next, you need to copy the appropriate lines for the database tables you want to alter. As discussed earlier, you can choose to convert a few database tables to InnoDB, or just convert them all.
• Paste the appropriate lines into the SQL box and run the commands.

That's it! Please check if the database works correctly.

DISCLAIMER: The scripts provided in our knowledgebase are for informational purposes only. We do not provide any warranty or support. It is essential to review and modify the scripts to fit your site's specific needs. There may be unforeseen outcomes when adding new script code to an existing website. You should discuss it with your website manager and seek advice from an experienced website developer if you are unsure.

Updated by SP on 14/12/2022