Rick James and I have created the (free, GPL) Index WP MySQL for Speed plugin. It addresses some of the performance problems with wp_postmeta by adding database indexes suitable for that table’s use cases. I use it myself on a Woo site with several thousand products. (It works on users, terms, options, and comments too.)
If you’d prefer not to use a plugin, you can do the same work with phpmyadmin or some similar tool. Here’s the SQL Data Definition Language the plugin uses to adjust wp_postmeta.
Before you do this make sure…
- you’re using MySQL InnoDB tables
- MySQL/MariaDB 5.7+
- you have a backup
ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);
These adjusted / added database keys match the common query patterns from WP core and Woo.