We’ve got a WP+woocommerce site that is over 8 years old. The site has over 60,000 users and a similar number of orders. The wp_postmeta has over 4,000,000 records and wp_usermeta has over 1,500,000. This is causing all kinds of issues because the site was not updated regularly. The site wants to update the DB and it crashes every time, likely because of these tables.
Any ideas?
Modern MariaDB and MySQL versions allow your tables’ keys to be more efficient. Better keys can help a lot to speed up the kinds of database queries WooCommerce (and core WordPress) use.
If you have a recent MySQL version that can handle the DYNAMIC row format, these postmeta keys will help a lot.
PRIMARY KEY (`post_id`, `meta_key`, `meta_id`),
UNIQUE KEY `meta_id` (`meta_id`),
KEY `meta_key` (`meta_key`, `meta_value`(32), `post_id`, `meta_id`),
KEY `meta_value` (`meta_value`(32), `meta_id`)
This plugin installs those database keys for you. To avoid timeouts when installing the keys, you should run it with wp-cli.
This plugin mitigates some database inefficiencies with many users.