Optimise and rebuild Wordpress Meta Table Indexes
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