Optimise and rebuild Wordpress Meta Table Indexes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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`; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment