WordPress Database Optimisation: How to Keep Your Site Running Fast

WordPress will keep just the five latest revisions per post and bin the rest automatically. Set it to false and revisions disappear completely, though we’d recommend keeping a few around as backup against those moments when you accidentally delete something important.

Delete the revisions you’ve already got piling up with a direct SQL query: Don’t forget to clean up the orphaned metadata lurking in wp_postmeta after you’ve deleted those revisions. Auto-drafts get created every time someone opens the post editor and they need the same treatment. Our WordPress development team can write custom cleanup scripts for your particular setup if running raw SQL against your live database makes you nervous.

Every WordPress website relies on a MySQL database to store its content, settings, user accounts and plugin data. Over time, that database accumulates overhead in the form of orphaned records, expired transients and revision histories that serve no practical purpose. The result is a site that gradually slows down, with page load times creeping upward as queries take longer to execute. If your site has been running for more than a year without any database maintenance, there is a good chance it is carrying unnecessary weight. WordPress support and maintenance for business websites includes database health checks as part of ongoing site care, ensuring your database stays lean and your site stays responsive.

Even small business websites collect database junk faster than you’d think. Your simple blog with a contact form starts accumulating thousands of redundant rows within months and WooCommerce stores make things worse. But cleaning up WordPress databases isn’t rocket science once you know what you’re looking at. We’ll walk you through the actual steps here, from spotting the bloat to setting up maintenance that won’t drive you mad.

Understanding the WordPress Database Structure

WordPress creates twelve core tables when you install it. The wp_posts table handles all content including revisions, wp_postmeta stores custom fields and SEO data, whilst wp_options contains site settings and those transient cache records that pile up everywhere.

Most plugins dump their own tables into your database without asking. Security plugins create login logs, analytics tools store visitor tracking, form builders keep submission records. These tables keep growing even after you’ve deleted the plugins that created them. And that’s where your database starts getting sluggish. The WordPress wpdb class documentation explains how WordPress talks to the database, which helps you understand why this mess happens in the first place. Knowing your database structure means you can target the real problems instead of running random cleanup tools and hoping for the best.

Identifying Database Bloat

Before you start fixing anything, you’ve got to measure what’s broken. Jump into phpMyAdmin through your hosting control panel and take a look at your WordPress database. Every table shows up with row counts and file sizes right there, so you can spot which ones are hogging space and whether all that data is serving a purpose.

Watch out for tables that look way too big compared to what your site does. Your wp_posts table shouldn’t have fifteen thousand rows when you’ve only published two hundred blog posts, but revisions and auto-drafts will do exactly that. Same story with wp_options tables that hit several megabytes because of expired transients and plugin data that never gets cleaned up. And about wp_options: WordPress loads every single row marked with autoload set to “yes” on every page request, so a bloated autoload dataset means your server’s working harder than it needs to for every visitor.

Clearing Transients and Expired Data

If you prefer not to run SQL queries directly, plugins like WP-Optimize handle transient cleanup through a visual interface without touching the database manually.

WordPress includes transients as a built-in caching system that plugins and themes rely on for storing temporary data like API responses or complex query results. This prevents recalculating the same information every time someone loads a page. But here’s where things get messy. WordPress sets an expiration time for each transient yet won’t actively delete them when they expire. It only removes expired transients if something specifically asks for that data and discovers it’s gone stale. Otherwise they just sit there taking up space forever.

Thousands of expired transients can pile up on plugin-heavy sites, cluttering your wp_options table and making that autoload problem we mentioned even worse. You can clear out expired transients with a targeted query: WHERE option_name LIKE ‘_transient_timeout_%’ AND option_value < UNIX_TIMESTAMP(); Then run a second query for the actual transient values: WHERE option_name LIKE ‘_transient_%’ AND option_name NOT LIKE ‘_transient_timeout_%’; Don’t rush into this on a live site though. Some transients are still being used and removing them forces plugins to rebuild that data from scratch, which might slow things down temporarily. Schedule this for quiet periods and consider using a maintenance plugin that handles transient cleanup automatically.

Optimising Tables and Reducing Overhead

Normal database operations create overhead in MySQL tables. Deleted rows don’t free up their space immediately but instead get marked as available for future use. MySQL might reuse this space or it might not. Tables end up larger than they need to be and queries can slow down because the database has to work through all that fragmented storage.

MySQL’s OPTIMIZE TABLE command sorts out this wasted space and defragments your table data. Run it through phpMyAdmin by selecting your tables and picking “Optimize table” from the dropdown or just fire off the SQL command directly:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

Your table gets locked while this runs, which means you’ll want to schedule it during quiet hours if your site’s busy. Small to medium WordPress sites? We’re talking seconds. But if you’ve got millions of rows then you’re looking at a longer wait. Monthly table optimisation stops fragmentation from reaching the point where your users notice things slowing down. The MySQL documentation on OPTIMIZE TABLE explains all the technical bits and what won’t work.

Check for orphaned data that old plugins left behind too. for tables with prefixes that don’t match your WordPress setup. Your prefix is wp_ but you’re seeing wfm_ or icl_ tables? Those came from plugins that aren’t running anymore. Double check the plugin’s gone before you drop those tables, then clear them out to free up space and tidy things up.

Using WP-CLI for Database Maintenance

WP-CLI gives you proper command-line tools for database maintenance that beat browser alternatives hands down if you’ve got SSH access. Most managed WordPress hosts include WP-CLI as standard since it’s the official WordPress command-line interface. Sites running on WordPress managed hosting usually have WP-CLI ready to go.

WP-CLI makes database cleanup dead simple with a few commands. Run these to search and delete post revisions, clean up transients and optimise all database tables. Stick them in a weekly or monthly cron job and you’ve got automated maintenance that keeps everything lean without lifting a finger. Export your database first using WP-CLI’s backup features so you can roll back if something breaks. The WP-CLI handbook covers every command you’ll need.

wp post delete $(wp post list --post_type='revision' --format=ids) --force

wp transient delete --expired

wp db optimize

SELECT SUM(LENGTH(option_value)) AS autoload_size
FROM wp_options

Run this quick SQL query in phpMyAdmin to check your autoloaded data: WHERE autoload = ‘yes’; Anything over a megabyte needs attention. Sites that perform well keep their autoloaded data way below that mark and the WordPress get_option documentation breaks down exactly why this autoload mechanism affects your performance.

Cleaning Up Post Revisions and Auto-Drafts

Post revisions will absolutely destroy your database size if you don’t keep them in check. WordPress creates a new revision every time you hit Save Draft or Update, which means one blog post edited twenty times becomes twenty complete copies of that content sitting in your database. Scale that across hundreds of posts and you’re looking at serious bloat.

You’ve got two ways to tackle revisions. Limit how many WordPress saves going forward by dropping a constant into your wp-config.php file:

Building a Long-Term Database Maintenance Routine

Quick cleanups work brilliantly but the mess comes back without ongoing maintenance. We combine automated scheduled tasks with manual reviews when needed. Automated jobs handle the predictable like expired transients, spam comments and table overhead. Manual work covers the decisions that need human judgment, like spotting abandoned plugin tables or finding autoloaded options that shouldn’t be autoloading.

• Weekly: clear expired transients and spam comments automatically via WP-CLI or a maintenance plugin.
• Monthly: run table optimisation to reclaim fragmented space and review database size trends.
• Quarterly: audit plugin tables for orphaned data, review autoloaded options and check that revision limits are still appropriate.
• Before major updates: take a full database backup and note the current database size for comparison after the update.

Always backup before touching the database manually. Export through phpMyAdmin, WP-CLI or your hosting provider’s tools because even safe operations can go wrong with one mistyped query. Having a recent backup means a quick restore instead of disaster recovery. Professional WordPress maintenance and security services handle these routines with proper backups and monitoring built in.

Start with a cleanup, set up a schedule and your database will thank you for it with consistently quick response times. Those fractions of seconds you save from each cleanup don’t seem like much on their own, but they stack up fast when you’re running hundreds of queries per page load. Good hosting and proper caching help, but a well-maintained database sits at the heart of every fast WordPress site. It’s not glamorous work and you won’t get any awards for it. But this is exactly what separates sites that keep performing well from the ones that slowly grind themselves into the ground.