InnoDB vs MyISAM

One of the biggest reasons that large-scale MySQL 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. currently, InnoDB has been shown to perform better and be more reliable.  A big reason to use InnoDB over MyISAM is the lack of full table-level locking.

What is different between InnoDB & 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.

Firstly 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 the example below, you can see that two of the tables are still using MyISAM.

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'