One of the most important parts of a WordPress website, or any other dynamic/CMS (content management system) website, is the database. Your site can only perform at its best if your database is optimised and well taken care of. That means your database requires a little TLC every once in a while, to take such actions as cleaning up transients and old data, as well as ensuring that you’re using the most up to date and appropriate storage engine for your database. In our two-part MySQL Health Check series, we will be focussing on your WordPress database’s storage engine, including what storage engines are, how to determine which type your site uses, and finally, how to update to the latest option.
First of all, what is a storage engine?
A storage engine is the underlying software that your database uses to handle database operations, such as creating, reading, updating and deleting data.
There are many different storage engines available. MySQL comes with quite a few by default such as MyISAM, CSV, Merge, Archive, InnoDB, and many more. The most popular storage engines for MySQL are MyISAM and InnoDB.
MyISAM was the default storage engine for all MySQL installations prior to version 5.5, while InnoDB was made the default storage engine in version 5.5 and above. At the time of writing, the latest MySQL version is 8.0. If you’re using MariaDB, InnoDB became the default storage engine in version 10.2. The current MariaDB version at the time of writing is 10.5.
Whilst all storage engines have their pros and cons depending on what your main goal is, InnoDB is generally accepted as the superior storage engine for WordPress sites. And yet, many WordPress sites are still currently using MyISAM, as that was the default option at the time those sites were built.
What are the benefits of using InnoDB?
Transactions
InnoDB is a storage engine that supports transactions, whereas MyISAM does not. A transaction is simply the act of performing one or more changes to your database, usually in a series of updates. Many database transactions affect multiple tables at once. For example, if a customer places an order on your website, that data may need to be logged in multiple different tables as part of the overall transaction. Such as updating the products, orders, and customer tables.
When a transaction occurs whilst using InnoDB, it must be completed in full. If it is not, all changes are rolled back. This is very important in maintaining data integrity and ensuring consistency in your database. If you are using MyISAM however, and the server or MySQL server crash halfway through performing an order for a customer, you may end up with a half-completed order, where only some tables are updated. InnoDB is superior in this sense as it will roll-back the transaction on recovery. The incomplete order will not be saved to the database.
Caching in Memory
Another big plus from using InnoDB is its ability to cache data from databases, and store it in memory. This can speed up WordPress sites by quite a bit, as it will read data from the hard disk once, and then subsequent reads will be stored in memory for faster access. If you’ve read up on using server-side caching before, such as OPcache, then this may be familiar to you.
There are a whole host of benefits to using InnoDB for your WordPress site. Some of the key benefits, including those already listed above, are as follows:
- Transaction based storage engine – ensures data integrity.
- Less likely to experience database crashes.
- Stores indexes and data in memory. Data in memory is much, much faster than when stored on a hard disk.
- Row-level locking is much faster and more efficient vs table-level locking.
- Allows other transactions to occur simultaneously.
- Can take advantage of multiple concurrent CPU cores.
How to determine whether you’re using MyISAM or InnoDB
The storage engine that your WordPress database is using is dependent on what your server’s default storage engine was set to when you first installed WordPress. For many older sites this is likely MyISAM.
There are a few different ways to identify your current storage engine. The current easiest way is via phpMyAdmin:
- Open up phpMyAdmin in your cPanel or Plesk account.
- Select your database on the left hand side. This will list all tables in your database.
- View your storage engine under the ‘Type’ column. This should be either MyISAM or InnoDB.
If you’re using InnoDB for all tables you can stop right there! If you’re using MyISAM for some or all tables, check out part 2 of our MySQL Health Check series for the full steps to update your database to InnoDB!
As always, if you have any questions about this post or our shared hosting, VPS, reseller or dedicated server plans, simply call us on 1300 MY HOST (694 678) during business hours, or submit a ticket through our Support Portal and one of the crew will be in touch!
