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 the phpMyAdmin database manager.
  
  
  
  
• In the left menu of phpMyAdmin, choose the applicable database. Take 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:
  
  
  
  
• Go to the SQL tab and enter the following SQL commands into the box.
  On the third line, replace name_of_your_db with the actual database name!

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

• The results under the "sql_statements" column will appear incomplete at first. It's important to get the full text.
  • Tick the checkbox next to "Show All" and confirm that you want to see all of the rows by clicking on the OK button.
  • Next, you should see an "Extra Options" button. Click on it, change the option 'partial texts' to 'full texts' and click on the "Go" button:
    
    
    
    
• You should get results that look something like this:
  
  
  
  It's important that all lines end with ENGINE=InnoDB; and are not truncated. Otherwise, please assure that you've applied the previous step correctly.
  
  
• Next, you need to copy all lines or just the appropriate lines for the database tables you want to alter.
  As mentioned earlier, you can choose to convert a few database tables to InnoDB, or just convert them all.
• In the left menu of phpMyAdmin, make sure to choose the applicable database again, as phpMyAdmin might have switched to the information_schema database in the meantime:
  
  
• Click on the SQL tab, paste the copied lines / SQL commands into the text area (after emptying any pre-filled commands) and click on the "Go" button:
  
  
  
  
• Finally, you should see a confirmation that all respective tables were converted:
  
  
  
  

That's it! Please check if all respective database tables were converted, and the database and website work correctly.

Notes:

• If your tables contain default values using "zero" dates, such as 0000-00-00 00:00:00, it's because we use the NO_ZERO_DATE option on our database server. Then it's necessary to allow invalid dates by adding SET SQL_MODE='ALLOW_INVALID_DATES'; at the top of the ALTER TABLE queries.
• If something has went wrong during the conversion process, you can download another backup of the current database (as a precaution), drop all current database tables, and import the backup that you've exported earlier.
• If importing the backup doesn't work for some reason, please upload the backup to the home directory of your account and open a ticket at our technical support department so our technicians can attempt to import it.

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 01/10/2024