How to Clean Up Your
WordPress Database for SEO

The connection between WordPress database health and SEO is indirect but measurable. Google’s Core Web Vitals use Time to First Byte (TTFB) as a primary signal for Interaction to Next Paint (INP) and Largest Contentful Paint (LCP) scoring. A bloated WordPress database increases the time your server spends executing queries before it can even begin sending HTML to the browser — directly inflating TTFB and suppressing your Core Web Vitals scores.

On a site with years of content, plugin churn, and no database maintenance, it is common to find databases that are 5–20× larger than necessary. The performance impact is not hypothetical: removing post revisions and transients alone can reduce average query execution time by 30–60% on high-bloat databases — measurable in your server response logs within 24 hours of cleanup.

How Database Bloat Affects SEO and Core Web Vitals

WordPress stores almost everything in its MySQL database — posts, pages, options, user data, comments, metadata, and the accumulated residue of every plugin that has ever been installed. Over time, without active maintenance, the database accumulates rows that are never queried but slow down every lookup that does happen.

The SEO impact compounds beyond raw speed. Google’s crawl budget — the number of pages Googlebot crawls on your domain per day — is partially governed by your server response time. A site with TTFB above 500ms will find Googlebot crawling fewer pages per session and potentially missing freshly published content for days after publication. Reducing TTFB through database optimisation improves crawl efficiency directly.

Anatomy of WordPress Database Bloat

WordPress stores data across a set of core tables, each with its own bloat characteristics. Understanding which tables accumulate waste and why helps you prioritise cleanup work and understand what each operation is doing.

Diagnosing Your Database Size and Bloat Sources

Before running any cleanup, you need a precise picture of your database’s current state — which tables are largest, how many revision rows exist, and how bloated your wp_options autoloaded data is. This baseline also lets you measure the impact of cleanup afterward.

-- Count post revisions (post_type = 'revision')
SELECT COUNT(*) AS revision_count
FROM wp_posts
WHERE post_status = 'inherit'
  AND post_type = 'revision';

-- Count expired transients in wp_options
SELECT COUNT(*) AS expired_transients
FROM wp_options
WHERE option_name LIKE '_transient_%'
   OR option_name LIKE '_site_transient_%';

-- Total autoloaded data size in wp_options (in KB)
SELECT ROUND(SUM(LENGTH(option_value)) / 1024, 2) AS autoload_kb
FROM wp_options
WHERE autoload = 'yes';

-- Count spam and trashed comments
SELECT comment_approved, COUNT(*) AS count
FROM wp_comments
GROUP BY comment_approved;

Cleaning Post Revisions and Autosaves

Post revisions are typically the single largest source of row bloat in the wp_posts table. WordPress saves a revision every time you click “Update” or every 60 seconds via autosave, with no limit by default. On a site with 3 years of regular publishing, it’s common to find 20,000–80,000 revision rows outnumbering actual published posts by a factor of 10 or more.

Step 1: Limit Future Revisions First

Before deleting existing revisions, set a limit so the problem doesn’t immediately recur. Add this line to your wp-config.php file, above the line that reads /* That's all, stop editing! */:

/* Limit post revisions to 5 per post */
define( 'WP_POST_REVISIONS', 5 );

A value of 3–5 is appropriate for most editorial workflows. If you need no revision history at all (e.g., a site managed by a single developer), you can set this to false — but retaining a small number of revisions provides a safety net for accidental content overwrites.

Step 2: Delete Existing Revision Backlog via SQL

-- Delete all post revisions and autosave drafts
DELETE FROM wp_posts
WHERE post_type = 'revision';

-- Clean up orphaned postmeta left by deleted revisions
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Removing Expired Transients and Option Bloat

The wp_options table is the most consequential bloat source for page load performance because WordPress autoloads a significant portion of this table on every single request — before any content rendering begins. Autoloaded data above 800KB noticeably increases TTFB; above 2MB, the performance impact becomes severe on shared and budget hosting.

Clearing Expired Transients

Transients are temporary data stored in wp_options by WordPress core and plugins. They have expiry times, but WordPress only deletes them when they are accessed after expiry — not proactively. A site that stored thousands of transients that were never re-accessed after their expiry accumulates them indefinitely.

-- Delete all expired transients (safe to run)
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
   OR option_name LIKE '_site_transient_%';

-- After clearing transients, verify autoload size improved
SELECT ROUND(SUM(LENGTH(option_value)) / 1024, 2) AS autoload_kb
FROM wp_options
WHERE autoload = 'yes';

Identifying Bloated Autoload Rows

After clearing transients, if your autoloaded data is still above 800KB, run this query to identify the specific options contributing the most weight. Large autoloaded rows are typically left by inactive plugins that stored settings or cached data and never cleaned up:

-- Top 20 largest autoloaded options by size
SELECT option_name,
       LENGTH(option_value) AS size_bytes,
       ROUND(LENGTH(option_value) / 1024, 1) AS size_kb
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size_bytes DESC
LIMIT 20;

For each large autoloaded option you identify, research which plugin created it before deleting it. Options from active, essential plugins should not be removed. Options from deactivated or replaced plugins — identifiable by the plugin’s slug in the option_name — can be safely deleted.

Clearing Orphaned Data and Spam

Beyond revisions and transients, a thorough database cleanup addresses several other categories of orphaned and stale data that accumulate invisibly over the life of a WordPress site.

Trashed Posts, Pages, and Auto-Drafts

-- Delete trashed posts and pages permanently
DELETE FROM wp_posts
WHERE post_status = 'trash';

-- Delete auto-draft content (unsaved editor sessions)
DELETE FROM wp_posts
WHERE post_status = 'auto-draft';

-- Clean up postmeta for any newly orphaned post IDs
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Spam and Trashed Comments

-- Delete spam comments
DELETE FROM wp_comments
WHERE comment_approved = 'spam';

-- Delete trashed comments
DELETE FROM wp_comments
WHERE comment_approved = 'trash';

-- Clean up orphaned commentmeta
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;

Optimising Database Tables

After deleting large volumes of rows, MySQL tables retain the disk space that was occupied by those rows as “overhead” — allocated but empty space within the table file. Running an OPTIMIZE TABLE command reclaims this space, defragments the table, and rebuilds indexes for faster queries. This is the final step of every cleanup cycle.

-- Optimise the tables most affected by cleanup
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_options;
OPTIMIZE TABLE wp_comments;
OPTIMIZE TABLE wp_commentmeta;

Measuring TTFB Before and After

To quantify the SEO performance improvement from your database cleanup, measure TTFB in Google Search Console’s Core Web Vitals report, PageSpeed Insights, or directly in Chrome DevTools (Network tab → first HTML document request → “Waiting (TTFB)” row). Record your TTFB baseline before cleanup and re-measure 48–72 hours after, allowing server-side caching to warm and measurements to stabilise.

Automating Ongoing Database Maintenance

A one-time database cleanup is valuable; a site that never accumulates significant bloat again is better. Automating maintenance prevents the gradual re-accumulation of revisions, transients, and orphaned data between manual cleanups.

Plugin-Based Automation

The most practical approach for most WordPress sites is a maintenance plugin that handles scheduled cleanup without requiring repeated manual SQL work. Three reliable options:

• WP-Optimize — purpose-built for database cleanup. Schedules revision deletion, transient cleanup, and table optimisation on a daily or weekly basis. Handles cleanup without requiring phpMyAdmin access.
• Advanced Database Cleaner — more granular control over what is cleaned, with the ability to preview row counts before deleting. Useful if you want to audit before automating.
• WP-Sweep — lightweight option focused specifically on orphaned data cleanup. Pairs well with a caching plugin that handles transient management separately.

wp-config.php Hardening

Beyond scheduled cleanup, several wp-config.php constants reduce database writes at the source — preventing bloat before it starts:

/* Limit revisions (5 is a sensible default) */
define( 'WP_POST_REVISIONS', 5 );

/* Increase autosave interval to reduce revision frequency */
/* Default is 60 seconds; 300 seconds is reasonable */
define( 'AUTOSAVE_INTERVAL', 300 );

/* Empty trash automatically after 7 days */
define( 'EMPTY_TRASH_DAYS', 7 );

More WordPress SEO Guides

This guide is part of indxq’s complete WordPress SEO series — covering performance, plugins, indexing, and technical configuration: