MySQL Storage Engine – How to Convert MyISAM to InnoDB
- Category : Server Administration
- Posted on : Dec 21, 2021
- Views : 879
- By : HostSEO
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.
Login to phpMyAdmin and click into your mySQL database.
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.
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;
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 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.
- cPanel Question 47
- cPanel Software Management 29
- cPanel Tutorials 13
- Development 29
- Domain 13
- General 19
- Linux Helpline (Easy Guide) 156
- Marketing 47
- MySQL Question 13
- News 2
- PHP Configuration 14
- SEO 4
- SEO 42
- Server Administration 84
- SSL Installation 54
- Tips and Tricks 24
- VPS 3
- Web Hosting 44
- Website Security 22
- WHM questions 13
- WordPress 148
Subscribe Now10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!
Posted on : Sep 17
Posted on : Sep 10
Posted on : Aug 04
Posted on : Apr 01
- server load
- ddos mitigation
- layer 7
- seo vpn
- Hosting Security
- smtp relay
- VPS Hosting
- cheap vpn
- Small Business
- SSD Hosting
- Link Building