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
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_';
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.