How to fix conversion error from InnoDB to MyISAM?

Question

How can I fix conversion error from InnoDB to MyISAM for these tables?

MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
MariaDB [db_name]> ALTER TABLE wp_postmeta ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

+-------------------+------------+-------------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                                              | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-------------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY                                               |            1 | ID             | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name                                             |            1 | post_name      | A         |      419351 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            1 | post_type      | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            2 | post_status    | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            3 | post_date      | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            4 | ID             | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent                                           |            1 | post_parent    | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author                                           |            1 | post_author    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_modified                   |            1 | post_modified  | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_post_type  |            1 | post_type      | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_post_type  |            2 | post_status    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_post_type  |            3 | post_mime_type | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_passw_post_statu |            1 | post_type      | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_passw_post_statu |            2 | post_password  | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_passw_post_statu |            3 | post_status    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_id         |            1 | post_type      | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_id         |            2 | post_status    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_id         |            3 | ID             | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_date                       |            1 | post_date      | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+-------------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   |     | NULL                |                |
| post_title            | text                | NO   |     | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(255)        | NO   |     |                     |                |
| post_name             | varchar(200)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext            | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+

+----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                | Non_unique | Key_name                                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_postmeta |          0 | PRIMARY                                 |            1 | meta_id     | A         |     3777303 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | post_id                                 |            1 | post_id     | A         |     1259101 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | wp_postme_idx_meta_key_post_id |            1 | meta_key    | A         |       23906 |     NULL | NULL   | YES  | BTREE      |         |               |
| wp_postmeta |          1 | wp_postme_idx_meta_key_post_id |            2 | post_id     | A         |     3777303 |     NULL | NULL   |      | BTREE      |         |               |
+----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

Answer 1

NO! That's the wrong solution to a memory issue.

The buffer_pool is a "cache"; it does not have to be bigger than the dataset.

InnoDB is faster than MyISAM; this point, alone, may doom your conversion to disaster.

The 1000 comes from 4*255, where the 4 comes from using ut8mb4 (good) and WP's default of using VARCHAR(255) (not good). Check your data, probably you come no where near to using 255 characters in any of those columns. Or maybe sometimes you have exceed 255 and had the data silently truncated!.

Here are tips on speeding up postmeta and making it smaller: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Another bulky thing that WP unnecessarily does: BIGINT (8 bytes) for lots of columns. A 4-byte INT (2 billion max) would save a lot of space. (But that is rather tedious to fix.

For the main performance boost (Caveat: I have not verified it):

ALTER TABLE wp_postmeta
    DROP PRIMARY KEY,
    ADD PRIMARY KEY(post_id, meta_key),
    ADD INDEX(meta_id) ;