Improve WordPress & WooCommerce DB indexes for all meta tables

Update 12/2022: There’s a pre-made plugin called Index WP MySQL for Speed which does the same thing.

The database architecture of WordPress looks essentially the same it did 15 years ago in v3.x. Itâs antiquated, outdated and very very far from optimal. But thereâs things one can do to improve the performance anyways. Iâve been working on a shop with many gigabytes of postmeta tables, which will reduce site responsiveness or worse make certain parts or your entire site unusable at all.

If you want more details read this explanation and if you just want to fix the MySQL indexes, then backup your database then run these MySQL statements (with caution): Optimise and rebuild WordPress Meta Table Indexes. This should preferably be done on a fresh installation. If you have already millions of entries in your tables, you need to make sure the fields used for the new indexes arenât NULL. This will still allow for duplicates for a single meta_key for the same post_id.

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 WHERE meta_value IS NOT NULL;
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 WHERE meta_value IS NOT NULL;
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 WHERE meta_value IS NOT NULL;
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 WHERE meta_value IS NOT NULL;
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 WHERE meta_value IS NOT NULL;
RENAME TABLE `wp_woocommerce_order_itemmeta` TO `wp_woocommerce_order_itemmeta_old`;
RENAME TABLE `wp_woocommerce_order_itemmeta_new` TO `wp_woocommerce_order_itemmeta`;

This will duplicate all your meta tables, rename the existing ones by appending _old to their names. If everything works fine, you can delete the _old tables like this:

DROP TABLE wp_postmeta_old;
DROP TABLE wp_commentmeta_old;
DROP TABLE wp_termmeta_old;
DROP TABLE wp_woocommerce_order_itemmeta_old;

I have done this on multiple WordPress & WooCommerce installations, some with 10GB+ sized databases. Always without any problems or unexpected behaviour. In my experience this makes the whole website respond quicker and snappier and one can instantly feel the performance improvement.