A well-optimized database is essential for WordPress performance. Here’s how to clean, optimize, and maintain your database.

🗄️ Understanding WordPress Database Structure

WordPress uses 12 core tables plus custom tables from plugins:

Core WordPress Tables:
├── wp_commentmeta
├── wp_comments
├── wp_links
├── wp_options
├── wp_postmeta
├── wp_posts
├── wp_termmeta
├── wp_terms
├── wp_term_relationships
├── wp_term_taxonomy
├── wp_usermeta
└── wp_users

The Most Important Tables:

• wp_posts: All content (posts, pages, products)
• wp_postmeta: Metadata for posts
• wp_options: Site settings
• wp_comments: Comments on content

🔍 Why Databases Get Bloated

Common Causes:

1. Post revisions (hundreds per post)
2. Spam comments
3. Orphaned metadata
4. Transients
5. Incomplete plugin/cron data

The Impact:

• Slower queries
• Larger backup files
• More memory usage
• Longer page load times

🛠️ How to Optimize

1. Remove Post Revisions

Post revisions can accumulate quickly and bloat the wp_posts table.

Set Revision Limit:

// In wp-config.php
define('WP_POST_REVISIONS', 5);Code language: JavaScript (javascript)

Delete All Revisions:

-- Query to delete all revisions
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';Code language: JavaScript (javascript)

2. Clean Up Spam Comments

Spam comments add no value and waste space.

-- Delete all spam comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';Code language: JavaScript (javascript)

3. Delete Orphaned Meta Data

When posts are deleted, metadata often remains.

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

-- Delete orphaned term meta
DELETE tm
FROM wp_termmeta tm
LEFT JOIN wp_terms t ON t.term_id = tm.term_id
WHERE t.term_id IS NULL;Code language: PHP (php)

4. Clean Transients

Transients are temporary cached data. They can accumulate.

-- Delete expired transients
DELETE FROM wp_options WHERE option_name LIKE '_transient_%';
DELETE FROM wp_options WHERE option_name LIKE '_transient_timeout_%';Code language: JavaScript (javascript)

Or use WP-CLI:

wp transient delete --allCode language: JavaScript (javascript)

5. Optimize Tables

After cleaning, optimize the tables.

Using WP-CLI:

wp db optimize

Using PHPMyAdmin:

1. Select all tables
2. Choose “Optimize table” from dropdown
3. Execute

6. Repair Tables (If Needed)

wp db repair

📊 Database Optimization Schedule

🚀 Performance Gains

Before Optimization:

• Table size: 500 MB
• Query speed: 1.5 seconds
• Page load: 4.2 seconds

After Optimization:

• Table size: 200 MB (60% reduction)
• Query speed: 0.3 seconds
• Page load: 2.1 seconds

🔧 Plugin Solutions