In this post, I will show you how to easily convert your WordPress database tables from the MyISAM engine to the InnoDB engine.
One of the biggest reasons that WordPress sites slow down because their database hasn’t been optimized. A lot number of older sites are still using the MyISAM storage engine in their database. A big reason to use InnoDB over MyISAM is the lack of full table-level locking. This allows your queries to process faster.
Are You Using MyISAM or InnoDB?
Check if any of your tables are using MyISAM instead of InnoDB
For WP-CLI
wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-root
For MYSQL-CLI
In case if your host doesn’t support WP-CLI then you can use MySQL-CLI.
use $database; SHOW TABLE STATUS WHERE Engine = 'MyISAM';Remember to change $database with your own database name
If the output is empty then there are no MyISAM tables, if you do get some results it will look like this:
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+------------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+------------------------+----------+----------------+---------+
| wp_postmeta | MyISAM | 10 | Dynamic | 13029 | 639 | 8338416 | 281474976710655 | 431104 | 0 | 132829 | 2019-08-24 13:43:04 | 2019-08-24 13:44:02 | 2019-08-24 13:43:04 | utf8mb4_unicode_520_ci | NULL | | |
| wp_posts | MyISAM | 10 | Dynamic | 2614 | 3197 | 8357092 | 281474976710655 | 314368 | 0 | 17561 | 2019-08-24 13:43:04 | 2019-08-24 13:43:04 | 2019-08-24 13:43:04 | utf8mb4_unicode_520_ci | NULL | | |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+------------------------+----------+----------------+---------+
Convert MyISAM to InnoDB
Be sure to make a backup of your site files and the database before following any of these instructions.
You can easily backup your database with these commands
For WP-CLI
wp db export database.sql --allow-rootFor MySQL
mysqldump -u $dbusername -p $dbname > database.sqlNow to convert tables from MyISAM to InnoDB run the below commands
For WP-CLI
wp db query "$(wp db query "SELECT CONCAT('ALTER TABLE ',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = '$(wp db size --format=csv --allow-root | awk -F, '{print $1}' | tail -1)'" --allow-root --silent --skip-column-names)" --allow-rootFor MySQL
For MySQL you will have to do some steps manually
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = '$database';Using the above in MySQL command will give you output similar to this
+----------------------------------------------------------------------+
| CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') |
+----------------------------------------------------------------------+
| ALTER TABLE database.wp_comments engine=InnoDB; |
| ALTER TABLE database.wp_posts engine=InnoDB; |
+----------------------------------------------------------------------+You can silent the columns and headers by invoking mysql with -sN flag and the output will be like this
ALTER TABLE gbvqjxudgq.wp_comments engine=InnoDB;
ALTER TABLE gbvqjxudgq.wp_posts engine=InnoDB;Simply copy-paste these queries in MySQL CLI and execute them this will convert all mentioned tables to InnoDB.
Now you can check if there are any MyISAM tables left, you should not get any output
wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-rootCompare with the InnoDB version
wp db query "SHOW TABLE STATUS WHERE Engine = 'InnoDB'" --allow-rootYou should get a large table output like this showing all tables were converted from MyISAM to InnoDB engine.
+------------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
| wp_auto_spinner_log | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | latin1_swedish_ci | NULL | | |
| wp_automatic_amazon_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_articles_keys | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_articles_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_cached | InnoDB | 10 | Compact | 5756 | 458 | 2637824 | 0 | 0 | 4194304 | 10255 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_camps | InnoDB | 10 | Compact | 9 | 10922 | 98304 | 0 | 0 | 0 | 3250 | 2019-07-22 08:30:18 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_automatic_categories | InnoDB | 10 | Compact | 272 | 60 | 16384 | 0 | 0 | 0 | NULL | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_clickbank_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_feeds_links | InnoDB | 10 | Compact | 4902 | 70 | 344064 | 0 | 0 | 0 | NULL | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_general | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_keywords | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:18 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_log | InnoDB | 10 | Compact | 5812 | 453 | 2637824 | 0 | 0 | 4194304 | 43023 | 2019-07-22 08:30:19 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_automatic_youtube_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_bv_fw_requests | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | 482 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_bv_ip_store | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 16384 | 0 | 1000000 | 2019-08-23 19:22:41 | NULL | NULL | binary | NULL | | |
| wp_bv_lp_requests | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_commentmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_ewwwio_images | InnoDB | 10 | Compact | 24364 | 532 | 12976128 | 0 | 10993664 | 9437184 | 112064 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_ewwwio_queue | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | NULL | 2019-07-22 08:30:19 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_options | InnoDB | 10 | Compact | 411 | 478 | 196608 | 0 | 16384 | 0 | 216781 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_postmeta | InnoDB | 10 | Compact | 12081 | 999 | 12075008 | 0 | 2129920 | 4194304 | 132836 | 2019-08-24 15:10:57 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_term_relationships | InnoDB | 10 | Compact | 3358 | 43 | 147456 | 0 | 114688 | 0 | NULL | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_term_taxonomy | InnoDB | 10 | Compact | 3417 | 71 | 245760 | 0 | 262144 | 0 | 4223 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_termmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_terms | InnoDB | 10 | Compact | 3416 | 71 | 245760 | 0 | 294912 | 0 | 4223 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_usermeta | InnoDB | 10 | Compact | 78 | 2940 | 229376 | 0 | 32768 | 0 | 217 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_users | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 0 | 4 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_yoast_seo_links | InnoDB | 10 | Compact | 9567 | 224 | 2146304 | 0 | 1064960 | 7340032 | 50106 | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_yoast_seo_meta | InnoDB | 10 | Compact | 12498 | 124 | 1556480 | 0 | 0 | 4194304 | NULL | 2019-07-22 08:30:19 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
+------------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+Sources
Stackoverflow – Convert all tables from MyISAM into InnoDB.
Stackoverflow – Suppress column header output for a single SQL statement.
