Optimise and rebuild Wordpress Meta Table Indexes

gistfile1.txt

CREATE TABLE wp_postmeta_new (
        meta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        post_id BIGINT UNSIGNED NOT NULL,
        meta_key VARCHAR(255) NOT NULL,
        meta_value LONGTEXT NOT NULL,
        PRIMARY KEY(post_id, meta_key, meta_id),  -- to allow dup meta_key for a post
        INDEX(meta_id),    -- to keep AUTO_INCREMENT happy
        INDEX(meta_key)
        ) ENGINE=InnoDB;
INSERT INTO wp_postmeta_new SELECT * FROM wp_postmeta;
RENAME TABLE `wp_postmeta` TO `wp_postmeta_old`;
RENAME TABLE `wp_postmeta_new` TO `wp_postmeta`;

CREATE TABLE wp_commentmeta_new (
        meta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        comment_id BIGINT UNSIGNED NOT NULL DEFAULT '0',
        meta_key VARCHAR(255) NOT NULL DEFAULT '',
        meta_value LONGTEXT,
        PRIMARY KEY(comment_id, meta_key, meta_id),  -- to allow dup meta_key for a post
        INDEX(meta_id),    -- to keep AUTO_INCREMENT happy
        INDEX(meta_key)
        ) ENGINE=InnoDB ;
INSERT INTO wp_commentmeta_new SELECT * FROM wp_commentmeta;
RENAME TABLE `wp_commentmeta` TO `wp_commentmeta_old`; -- rename wp_commentmeta to wp_commentmeta_old
RENAME TABLE `wp_commentmeta_new` TO `wp_commentmeta`; -- rename wp_commentmeta_new to wp_commentmeta

CREATE TABLE wp_termmeta_new (
        meta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        term_id BIGINT UNSIGNED NOT NULL DEFAULT '0',
        meta_key VARCHAR(255) DEFAULT '',
        meta_value LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
        PRIMARY KEY(term_id, meta_key, meta_id),  -- to allow dup meta_key for a post
        INDEX(meta_id),    -- to keep AUTO_INCREMENT happy
        INDEX(meta_key)
        ) ENGINE=InnoDB ;
-- create optimized indexes in new table
INSERT INTO wp_termmeta_new SELECT * FROM wp_termmeta;
RENAME TABLE `wp_termmeta` TO `wp_termmeta_old`; -- rename wp_termmeta to wp_termmeta_old
RENAME TABLE `wp_termmeta_new` TO `wp_termmeta`; -- rename wp_termmeta_new to wp_termmeta

CREATE TABLE wp_usermeta_new (
        umeta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL DEFAULT '0',
        meta_key VARCHAR(255) NOT NULL DEFAULT '',
        meta_value LONGTEXT,
        PRIMARY KEY(user_id, meta_key, umeta_id),  -- to allow dup meta_key for a post
        INDEX(umeta_id),    -- to keep AUTO_INCREMENT happy
        INDEX(meta_key)
        ) ENGINE=InnoDB ;
-- new table
INSERT INTO wp_usermeta_new SELECT * FROM wp_usermeta;
RENAME TABLE `wp_usermeta` TO `wp_usermeta_old`; -- rename wp_usermeta to wp_usermeta_old
RENAME TABLE `wp_usermeta_new` TO `wp_usermeta`; -- rename wp_usermeta_new to wp_usermeta

CREATE TABLE `wp_woocommerce_order_itemmeta_new` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_item_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) NOT NULL DEFAULT '',
  `meta_value` longtext,
  PRIMARY KEY(order_item_id, meta_key, meta_id),  -- to allow dup meta_key for a post
  INDEX(meta_id),    -- to keep AUTO_INCREMENT happy
  INDEX(meta_key)
) ENGINE=InnoDB AUTO_INCREMENT=14347600 ;
INSERT INTO wp_woocommerce_order_itemmeta_new SELECT * FROM wp_woocommerce_order_itemmeta;
RENAME TABLE `wp_woocommerce_order_itemmeta` TO `wp_woocommerce_order_itemmeta_old`;
RENAME TABLE `wp_woocommerce_order_itemmeta_new` TO `wp_woocommerce_order_itemmeta`;