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 postINDEX(meta_id),    -- to keep AUTO_INCREMENT happyINDEX(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 postINDEX(meta_id), -- to keep AUTO_INCREMENT happyINDEX(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_oldRENAME TABLE `wp_commentmeta_new` TO `wp_commentmeta`; -- rename wp_commentmeta_new to wp_commentmetaCREATE 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 postINDEX(meta_id), -- to keep AUTO_INCREMENT happyINDEX(meta_key)) ENGINE=InnoDB ;-- create optimized indexes in new tableINSERT INTO wp_termmeta_new SELECT * FROM wp_termmeta;RENAME TABLE `wp_termmeta` TO `wp_termmeta_old`; -- rename wp_termmeta to wp_termmeta_oldRENAME TABLE `wp_termmeta_new` TO `wp_termmeta`; -- rename wp_termmeta_new to wp_termmetaCREATE 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 postINDEX(umeta_id), -- to keep AUTO_INCREMENT happyINDEX(meta_key)) ENGINE=InnoDB ;-- new tableINSERT INTO wp_usermeta_new SELECT * FROM wp_usermeta;RENAME TABLE `wp_usermeta` TO `wp_usermeta_old`; -- rename wp_usermeta to wp_usermeta_oldRENAME TABLE `wp_usermeta_new` TO `wp_usermeta`; -- rename wp_usermeta_new to wp_usermetaCREATE 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 postINDEX(meta_id), -- to keep AUTO_INCREMENT happyINDEX(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`;

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.