Improving WordPress database performance with cleanup and maintenance

Clean up transients

Transients data is cache-like data in the WordPress database. When the number of transients grows too big, it makes querying the database slower.
The recommended approach here is to avoid storing transients within the MySQL database and use Redis, which is more appropriate as cache storage.

Speed up WordPress with Persistent Object Cache powered by latest Redis

When done configuring persistent cache in Redis, simply delete all existing DB-stored transient like so:

wp transient delete-all

Set up high-performance SQL indexes

Any CMS that uses MySQL, has some indexes set up in the database, for faster querying of data.
However, WordPress could do better in these regards. You can set up additional indexes that will help SQL queries to perform faster.
It can be done easily with the WP-CLI:

wp plugin install --activate index-wp-mysql-for-speed
wp index-mysql enable --all

The results might be not noticeable on smaller websites. But surely the large websites will benefit from the added indexes.

Duplicate post meta removal

By design, WordPress allows storing post meta data with duplicate meta_key and post_id.
This is to allow storing array-like data for a post. But it’s easy to tell that, for example, there should be only one _price meta for a given product in Woocommerce.
If you do have multiple _price values, it is likely a bug. To check this, run:

SELECT * FROM (SELECT post_id, COUNT(meta_id) AS c FROM `wp_postmeta` WHERE meta_key = '_price' GROUP BY post_id) AS t  WHERE c > 1;

It shows product IDs that have more than one price, for example:

+---------+-----+
| post_id | c   |
+---------+-----+
|  254335 |   2 |
|  260753 |   2 |
|  260986 |   2 |
|  253571 |   2 |
|  254359 |   2 |
|  257153 | 123 |
|  260235 |  60 |
+---------+-----+

Cleaning up can be done with this query:

DELETE FROM `wp_postmeta` WHERE meta_key = '_price' AND meta_id NOT IN (SELECT * FROM (SELECT MAX(meta_id) FROM `wp_postmeta` WHERE meta_key = '_price' GROUP BY post_id) AS t);

In fact, this appears to be a bug within Woocommerce, where it unnecessarily duplicates the storage of all variations’ prices into the base product’s price data.