Your WordPress site is slow. You've optimized images, installed a caching plugin, upgraded hosting. But the database? It's still bloated with years of accumulated junk.

Post revisions. Spam comments. Expired transients. Orphaned metadata. Auto-drafts from posts you never published. All of it sits in your MySQL database, slowing down every single page load.

How Much Bloat Does Your Database Have?

SELECT 
    post_type, post_status, COUNT(*) as total
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY total DESC;

1. Delete Post Revisions

WordPress saves a new revision every time you click "Update." A post edited 50 times has 50 revisions stored in the database.

-- Check revision count
SELECT COUNT(*) as revision_count
FROM wp_posts WHERE post_type = revision;

-- Delete all revisions
DELETE FROM wp_posts WHERE post_type = revision;

-- Clean orphaned meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Impact: Sites with 500+ posts often have 5,000+ revisions. Removing them can shrink your database by 30-50%.

2. Remove Auto-Draft Posts

DELETE FROM wp_posts
WHERE post_status = auto-draft
  AND post_date < DATE_SUB(NOW(), INTERVAL 30 DAY);

3. Empty the Trash

DELETE FROM wp_posts WHERE post_status = trash;

4. Clean Up Spam and Trash Comments

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

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

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

5. Remove Expired Transients

DELETE a, b FROM wp_options a
JOIN wp_options b ON b.option_name = CONCAT(_transient_timeout_, SUBSTRING(a.option_name, 12))
WHERE a.option_name LIKE _transient_%
  AND a.option_name NOT LIKE _transient_timeout_%
  AND b.option_value < UNIX_TIMESTAMP();

6. Find Heavy Autoloaded Options

SELECT 
    option_name,
    LENGTH(option_value) as size_bytes,
    ROUND(LENGTH(option_value) / 1024, 2) as size_kb
FROM wp_options
WHERE autoload = yes
ORDER BY size_bytes DESC
LIMIT 20;

7. Remove Orphaned Term Relationships

DELETE tr FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON tr.object_id = p.ID
WHERE p.ID IS NULL;

8. Find Duplicate Post Meta

SELECT post_id, meta_key, COUNT(*) as duplicates
FROM wp_postmeta
GROUP BY post_id, meta_key
HAVING COUNT(*) > 1
ORDER BY duplicates DESC
LIMIT 20;

9. Optimize Tables After Cleanup

OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_comments;
OPTIMIZE TABLE wp_commentmeta;
OPTIMIZE TABLE wp_options;

Before You Run These Queries

Always backup first. These queries delete data permanently.

1. Export your database via phpMyAdmin or mysqldump

2. Test queries on a staging site first

3. Run SELECT versions before DELETE to preview affected rows

Automate It With AI

Remembering these queries and adapting them to your specific situation takes time. With AI2SQL, you can just say:

• "Delete all post revisions older than 6 months"

• "Find the largest autoloaded options"

• "Show me orphaned metadata from deleted posts"

AI2SQL generates the exact MySQL query for your WordPress database. No memorization needed.

The Results

• Smaller database size: 30-70% reduction is common

• Faster queries: Less data to scan means faster page loads

• Quicker backups: Smaller database = faster backup and restore

• Better hosting performance: Less memory usage on your server

Start your cleanup today at ai2sql.io.