In this post I will show you how to easily convert your WordPress database tables from the MyISAM engine to the InnoDB engine with WP-CLI. In case you are wondering why you would want to do this database conversion, this StackOverflow post highlights the improvements of InnoDB over MySIAM (taking advantage of multiple cores is pretty awesome 😉 ). Casey Friday found huge improvements in response time and reduced server load after the converting from MySIAM to InnoDB which you can read more about here. There are also some interesting
MySQL index differences between the two engines.
Let’s get started!
Converting WordPress Tables from MyISAM to InnoDB with WP-CLI
Check if any of your tables are using MyISAM instead of InnoDB
wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-rootIf you get no output at all then there are no MyISAM tables, if you do get some output 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_posts | MyISAM | 10 | Dynamic | 2579 | 1916 | 4943644 | 281474976710655 | 4224000 | 0 | 11861 | 2017-08-19 21:56:47 | 2017-09-07 03:55:17 | 2017-08-19 21:56:48 | utf8mb4_unicode_ci | NULL | | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+You should absolutely take a backup first!
You can easily back up the database with this command
wp db export before-engine-change.sql --all-tablespaces --add-drop-table --allow-rootYou can easily restore with this command
wp db import before-engine-change.sql --allow-rootHere is a short bash script to convert all WordPress MyISAM database tables to InnoDB with WP-CLI.
#!/usr/bin/env bash
# Author Mike https://guides.wp-bullet.com
# Purpose - Convert MyISAM tables to InnoDB with WP-CLI
# create array of MyISAM tables
WPTABLES=($(wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-root --silent --skip-column-names | awk '{ print $1}'))
# loop through array and alter tables
for WPTABLE in ${WPTABLES[@]}
do
echo "Converting ${WPTABLE} to InnoDB"
wp db query "ALTER TABLE ${WPTABLE} ENGINE=InnoDB" --allow-root
echo "Converted ${WPTABLE} to InnoDB"
doneDone!
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_commentmeta | InnoDB | 10 | Compact | 250 | 65 | 16384 | 0 | 49152 | 0 | 405 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_comments | InnoDB | 10 | Compact | 163 | 603 | 98304 | 0 | 81920 | 0 | 248 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_ewwwio_images | InnoDB | 10 | Compact | 1575 | 228 | 360448 | 0 | 425984 | 0 | 2110 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_options | InnoDB | 10 | Compact | 530 | 4235 | 2244608 | 0 | 49152 | 4194304 | 65960 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_popularpostsdata | InnoDB | 10 | Compact | 93 | 176 | 16384 | 0 | 0 | 0 | NULL | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_popularpostssummary | InnoDB | 10 | Compact | 940 | 87 | 81920 | 0 | 114688 | 0 | 1154 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_postmeta | InnoDB | 10 | Compact | 3326 | 472 | 1572864 | 0 | 344064 | 4194304 | 16266 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_posts | InnoDB | 10 | Compact | 607 | 11174 | 6782976 | 0 | 131072 | 5242880 | 4791 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_prli_clicks | InnoDB | 10 | Compact | 1878 | 253 | 475136 | 0 | 1081344 | 4194304 | 2009 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_prli_groups | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 16384 | 0 | 3 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_prli_link_metas | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 32768 | 0 | 6 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_prli_links | InnoDB | 10 | Compact | 13 | 1260 | 16384 | 0 | 163840 | 0 | 16 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_relationships | InnoDB | 10 | Compact | 638 | 102 | 65536 | 0 | 16384 | 0 | NULL | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_taxonomy | InnoDB | 10 | Compact | 30 | 546 | 16384 | 0 | 32768 | 0 | 32 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_termmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_terms | InnoDB | 10 | Compact | 29 | 564 | 16384 | 0 | 32768 | 0 | 32 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_usermeta | InnoDB | 10 | Compact | 113 | 144 | 16384 | 0 | 32768 | 0 | 123 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_users | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 49152 | 0 | 5 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_yoast_seo_links | InnoDB | 10 | Compact | 877 | 168 | 147456 | 0 | 49152 | 0 | 3837 | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_yoast_seo_meta | InnoDB | 10 | Compact | 642 | 76 | 49152 | 0 | 0 | 0 | NULL | 2018-01-17 06:45:07 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
+------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+Sources
Converting Tables to InnoDB
Determine Table is MyISAM or InnoDB
Convert All Tables in Database to MyISAM or InnoDB
Wow! Thanks for another great WP-CLI tutorial! This is really helpful!
Always enjoy your posts! For converting tables to InnoDB, here is my compacted WP-CLI script using some SQL tricks.
wp db query "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME, ' ENGINE=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM'" --skip-column-names > db_optimize.sql
wp db query < db_optimize.sql
rm db_optimize.sql
Always love seeing how others solve similar challenges! Thanks for sharing Austin 🙂
thanks!