HostSEO Blog

Stories and News from IT Industry, Reviews & Tips | Technology Blog


MySQL Storage Engine – How to Convert MyISAM to InnoDB

One of the biggest reasons that large-scale WordPress sites slow down is because their database hasn’t been optimized. A lot of older sites are still using the MyISAM storage engine in their database. Over recent years, InnoDB has shown to perform better and be more reliable.  A big reason to use InnoDB over MyISAM, is the lack of full table-level locking. This allows your queries to process faster.

InnoDB vs MyISAM

Here are a few of the major differences between InnoDB and MyISAM:

  • InnoDB has row-level locking. MyISAM only has full table-level locking.
  • InnoDB has what is called referential integrity which involves supporting foreign keys (RDBMS) and relationship constraints, MyISAM does not (DMBS).
  • InnoDB supports transactions, which means you can commit and roll back. MyISAM does not.
  • InnoDB is more reliable as it uses transactional logs for auto recovery. MyISAM does not.

Are You Using MyISAM or InnoDB?

If you are running on a fairly new WordPress site chances are you are already using the InnoDB MySQL storage engine. But with older WordPress sites you might want to do a quick check. Some sites might even have mixed and matched MyISAM and InnoDB tables, in which you could see improvements by converting them all over.  Follow these simple steps below to check.

Step 1

Login to phpMyAdmin and click into your mySQL database.

Step 2

Do a quick scan or sort of the “Type” column and you can see which Storage Engine types your tables are using. In this example below, you can see that two of the tables are still using MyISAM.


Find MyISAM tables

Alternatively, you could run a query to see if any myISAM tables exist. Replace ‘database’ with your database name.

SELECT TABLE_NAME,
 ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database' and ENGINE = 'myISAM'

Convert MyISAM to InnoDB with phpMyAdmin

You can convert MyISAM to InnoDB fairly easily. This example is below is using the wp_comments table. Simply run the ALTER command to convert it to InnoDB storage engine. Note: We always recommend backing up your MySQL database before running any operations on it.

ALTER TABLE wp_comments ENGINE=InnoDB;
Ensure you are running MySQL 5.6.4 or higher otherwise, you might run into issues where full-text indexing is not supported yet by InnoDB. If you are a HostSEO client you don’t need to worry about this.

Alternatively, you can also convert them manually with phpMyAdmin. Simply click on the myISAM table, click into the “Operations” tab, and change the storage engine.


Convert MyISAM to InnoDB

Convert MyISAM to InnoDB with WP-CLI

WP-CLI is a command line tool for developers to manage common tasks (and not so common) of a WordPress installation. Check out this article on how to convert MyISAM to InnoDB with WP-CLI.

Subscribe Now

10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!

Archive Calendar

SatSunMonTueWedThuFri
 1
2345678
9101112131415
16171819202122
23242526272829
3031 

Born in 2004 ... Trusted By Clients n' Experts

SEO Stars

They never made me feel silly for asking questions. Help me understand how to attract more people and improve my search engine ranking.

Read More

Emily Schneller Manager at Sabre Inc
SEO Stars

Took advantage of Hostseo's superb tech support and I must say, it is a very perfect one. It is very fast, servers reliability is incredible.

Read More

Leena Mäkinen Creative producer
SEO Stars

We're operating a worldwide network of servers with high quality standards requirements, we’ve choose hostseo to be our perfect partner.

Read More

Ziff Davis CEO at Mashable
SEO Stars

It’s very comfortable to know I can rely about all technical issues on Hostseo and mostly that my website and emails are safe and secured here.

Read More

Isaac H. Entrepreneur
SEO Stars

With hostseo as a hosting partner we are more flexible and save money due to the better packages with great pricing, free SEO n' free SSL too!

Read More

Madeline E. Internet Professional