How to Convert Database Tables Storage Engine from MyISAM to InnoDB

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

For MySQL

mysqldump -u $dbusername -p $dbname >  database.sql

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

For 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;

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

WP-CLI

Stackoverflow – Convert all tables from MyISAM into InnoDB.

Stackoverflow – Suppress column header output for a single SQL statement.

Leave a Reply

Your email address will not be published. Required fields are marked *