My Account

Cleaning a Large WordPress Database

Certain types of WordPress websites are more demanding on the database than others. A growing database is a good way to uncover bad performant code. Tuning your PHP code for high performance is a complex topic and most likely involves working with theme and plugin authors for optimizations. Instead of doing all of that work, let’s go over some easy wins by reducing the overall database size.

Analyzing database usage with WP-CLI.

There is no “right” amount of database usage. Some tables can grow endlessly and not affect performance for other tables. However many tables – like wp_posts – are connected to other tables. So a large wp_postmeta table can slow down queries on the wp_posts table. Before we remove unnecessary bloat from the database, let’s take a look at the current database size. For total size, run wp db size --size_format=mb or to break down by each table run wp db size --size_format=mb --tables --all-tables.

Cleaning up with WP-CLI.

Always make a database backup before deleting anything.

wp db export --add-drop-table --default-character-set=utf8mb4

A good first step would be to check if any of the large custom tables are still in use. It’s common for plugins to leave data in the database when no longer in use. That’s so you can re-activate and begin using them again. If you do find one that you are certain is safe to remove then we can remove the tables manually by name.

wp db query "DROP TABLE IF EXISTS wp_mypluginlargetable"

WordPress transients can sometimes get stuck. A transient is a temporary piece of information stored in the database. It’s generally safe to remove them all. This can sometimes reclaim some database usage.

wp transient delete --all

Deleting old page/post revisions can help. While I wouldn’t recommend dumping everything, maybe you could start by deleting everything older than a few years back.

wp package install trepmal/wp-revisions-cli
wp revisions clean --before-date=2020-01-01

Hunting for orphaned database records with PHPmyadmin.

Checking for orphaned wp_postmeta records. 

SELECT * FROM wp_postmeta LEFT JOIN wp_posts
 ON wp_posts.ID = wp_postmeta.post_id
 WHERE wp_posts.ID IS NULL

If found, those can be safely removed with the following.

DELETE wp_postmeta FROM wp_postmeta LEFT JOIN wp_posts
 ON wp_posts.ID = wp_postmeta.post_id
 WHERE wp_posts.ID IS NULL

You can hunt for large meta_key within the wp_postmeta with the following.

SELECT meta_key, (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048576 AS `Size`, COUNT(*) AS `Count` FROM wp_postmeta
 GROUP BY `meta_key`
 ORDER BY `Size` DESC

If you find any large meta keys which are no longer actively used then you can remove them. Be sure to replace example_key_ with the name of the meta key you wish to remove.

DELETE FROM `wp_postmeta` WHERE meta_key LIKE 'example_key_';

Other recommendations

The wp_options table is complex. I’m not going to begin to discuss that here other than to reference a fantastic article by Kinsta, How to Clean up Your wp_options Table and Autoloaded Data. The WP-Sweep plugin covers many of these common areas and a few more. Lastly, never delete anything you don’t fully understand.

References