Blog / friday roundup
Five tools for a tidy WordPress database
Five tools we use to inspect and clean up bloated WordPress databases, with the checks and the order of operations we follow on live sites.
Most WordPress sites we look after never need anyone to think about the database. Then one day a site that used to feel quick starts hanging for three seconds on the dashboard, the nightly backup doubles in size, and nobody can say why. Nine times out of ten the files are fine and the database has quietly filled up with revisions, expired transients and leftovers from plugins that were removed years ago. Here are the five tools we actually open when that happens, roughly in the order we reach for them.
One thing before the list: take a working backup first, and make sure it is the kind you have restored at least once. Database cleanups are the sort of job that goes fine ninety-nine times and then deletes a table some plugin was still using. We wrote about the difference between having backups and having backups that restore, and it applies double here.
phpMyAdmin, for looking before touching
Every cPanel account has phpMyAdmin, and it is where we start. Sort the table list by size and you usually get your answer in ten seconds: a wp_postmeta that has grown to 400 MB, an wp_options with 90,000 rows, or a set of tables with a prefix belonging to a plugin that was deleted in 2021. We do not clean up from here if we can avoid it, because raw SQL on a live site is unforgiving, but we always diagnose from here. Knowing which table is fat changes which of the tools below is the right one.
WP-Optimize, for the routine bloat
For the ordinary cases, post revisions, auto-drafts, trashed comments, expired transients, this is the plugin we install, run, and uninstall. It handles the boring majority of database bloat without anyone needing to write a query. The two settings worth changing before you press anything: cap revisions rather than deleting all of them (clients do come back asking for last month's wording), and run the table optimization step separately so you can see what it reclaimed. On a site that has been publishing for a decade we often see a quarter of the database disappear on the first pass.
Advanced Database Cleaner, for plugin leftovers
WordPress plugins are good at installing things and bad at removing them. Deactivate and delete a page builder, a slider, an old security plugin, and its options rows, custom tables and scheduled cron events frequently stay behind forever. This plugin lists orphaned options, orphaned tables and orphaned cron tasks, and tells you which plugin each one came from where it can work it out. Read that attribution carefully: "unknown" does not mean unused, and we have seen people delete tables belonging to a plugin that was simply renamed. When we are not sure, we leave it.
Query Monitor, for the slow query behind the slow page
Size and speed are different problems. A 60 MB database can still crawl if something is running an unindexed query on every page load. Query Monitor sits in the admin bar and shows you, per page, which queries ran, how long they took and which plugin or theme fired them. More than once it has turned a vague "the site is slow" ticket into a one-line answer: a related-posts feature scanning every post on every request. Fix the cause and you may not need to clean anything.
WP-CLI, for doing it repeatably
If you look after more than a couple of sites, the command line beats clicking. wp db size --tables gives you the same picture as phpMyAdmin in one line, wp transient delete --expired clears the most common junk, wp post delete $(wp post list --post_type=revision --format=ids) deals with revisions, and wp cron event list shows the ghost schedules left by dead plugins. WP-CLI runs anywhere you have shell access, and it is the only way we would run this sort of job across a reseller's worth of sites. It is also safer than it looks, because every command does exactly one thing and you can see it before you run it.
A smaller database is worth having for its own sake: faster backups, faster restores, less to transfer when a site moves. Just do not expect it to fix a slow site on its own. Caching usually does more for front-end speed than any amount of table trimming, which we covered in our roundup of caching plugins, and the two jobs solve different problems. If your database has crossed a gigabyte and you would rather someone else looked at it first, send us a ticket through support and we will tell you what we see on the server side before you delete anything.