Converting WordPress Database Tables from MyISAM to InnoDB with WP-CLI

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-root

If 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-root

You can easily restore with this command

wp db import before-engine-change.sql --allow-root

Here 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"
done

Done!

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-root

Compare with the InnoDB version

wp db query "SHOW TABLE STATUS WHERE Engine = 'InnoDB'" --allow-root

You 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