Database Optimization & Repair

Overview

Regular database maintenance keeps your WordPress site running smoothly. WP-CLI provides powerful commands to check, repair, and optimize your database—essential for performance, reliability, and troubleshooting.

Understanding Database Maintenance

WordPress databases can degrade over time due to:

Table fragmentation - Wasted space from deleted data
Overhead - Unused space in tables
Corruption - Server crashes, improper shutdowns
Bloat - Revisions, transients, spam comments

WP-CLI helps you maintain database health through:

Check - Detect table errors
Repair - Fix corrupted tables
Optimize - Reclaim space and improve performance

Database Checking

Check All Tables

# Check all WordPress tables for errors
wp db check

What it checks:

Table structure integrity
Index corruption
Data consistency

Example output:

wp_posts                                   OK
wp_postmeta                                OK
wp_users                                   OK
wp_usermeta                                OK
wp_options                                 OK
wp_comments                                OK
wp_commentmeta                             OK

Check Specific Tables

# Check specific tables
wp db check wp_posts wp_postmeta

# Check tables with pattern
wp db tables | grep "woocommerce" | xargs wp db check

Automated Health Checks

#!/bin/bash
# db-health-check.sh

echo "=== Database Health Check ==="
echo "Date: $(date)"
echo ""

# Run check
if wp db check > /dev/null 2>&1; then
    echo "✓ All tables OK"
    exit 0
else
    echo "✗ Database issues detected!"
    echo "Running detailed check..."
    wp db check
    
    # Send alert
    echo "Database issues detected on $(hostname)" | mail -s "DB Alert" admin@example.com
    exit 1
fi

Add to cron for daily checks:

0 3 * * * /path/to/db-health-check.sh

Database Repair

Repair All Tables

# Repair all tables
wp db repair

When to use:

After server crash
"Table is marked as crashed" errors
Database corruption warnings
Site showing database errors

What it does:

Attempts to fix corrupted tables
Rebuilds damaged indexes
Recovers data when possible

Repair Specific Tables

# Repair specific table
wp db repair wp_posts

# Repair multiple tables
wp db repair wp_posts wp_postmeta wp_options

Emergency Repair Workflow

#!/bin/bash
# emergency-repair.sh

echo "=== Emergency Database Repair ==="

# 1. Backup first (if possible)
echo "Creating emergency backup..."
wp db export emergency-backup-$(date +%Y%m%d-%H%M%S).sql 2>/dev/null || echo "⚠ Backup failed, continuing with repair..."

# 2. Check database
echo "Checking database..."
wp db check

# 3. Repair
echo "Repairing database..."
wp db repair

# 4. Verify repair
echo "Verifying repair..."
if wp db check > /dev/null 2>&1; then
    echo "✓ Repair successful!"
else
    echo "✗ Repair incomplete - manual intervention required"
    exit 1
fi

# 5. Optimize after repair
echo "Optimizing database..."
wp db optimize

echo "=== Emergency Repair Complete ==="

Repair with MySQL Directly

# For severe corruption, use MySQL directly
wp db cli --execute="REPAIR TABLE wp_posts;"

# Repair all tables
wp db tables | xargs -I {} wp db cli --execute="REPAIR TABLE {};"

Database Optimization

Optimize All Tables

# Optimize all WordPress tables
wp db optimize

What it does:

Defragments tables
Reclaims unused space
Rebuilds indexes
Improves query performance

Benefits:

Faster queries
Reduced disk usage
Better performance

Optimize Specific Tables

# Optimize specific tables
wp db optimize wp_posts wp_postmeta

# Optimize large tables only
wp db query "
  SELECT table_name 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE() 
  AND data_length > 10485760
" --skip-column-names | xargs wp db optimize

Scheduled Optimization

#!/bin/bash
# weekly-optimization.sh

echo "=== Weekly Database Optimization ==="
echo "Date: $(date)"

# Get database size before
BEFORE=$(wp db query "
  SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE();
" --skip-column-names)

echo "Database size before: ${BEFORE}MB"

# Optimize
wp db optimize

# Get database size after
AFTER=$(wp db query "
  SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE();
" --skip-column-names)

echo "Database size after: ${AFTER}MB"

# Calculate savings
SAVED=$(echo "$BEFORE - $AFTER" | bc)
echo "Space reclaimed: ${SAVED}MB"

echo "=== Optimization Complete ==="

Add to cron (weekly on Sunday at 3 AM):

0 3 * * 0 /path/to/weekly-optimization.sh

Cleaning Database Bloat

Remove Post Revisions

# Count revisions
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type='revision';" --skip-column-names

# Delete all revisions
wp db query "DELETE FROM wp_posts WHERE post_type='revision';"

# Optimize after deletion
wp db optimize wp_posts

Clean Transients

# Delete expired transients
wp transient delete --expired

# Delete all transients (use with caution)
wp transient delete --all

# Count transients
wp db query "SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '_transient_%';" --skip-column-names

Remove Spam Comments

# Count spam comments
wp comment list --status=spam --format=count

# Delete spam comments
wp comment delete $(wp comment list --status=spam --format=ids)

# Optimize comments table
wp db optimize wp_comments wp_commentmeta

Clean Trashed Posts

# Count trashed posts
wp post list --post_status=trash --format=count

# Delete trashed posts permanently
wp post delete $(wp post list --post_status=trash --format=ids) --force

# Optimize posts table
wp db optimize wp_posts wp_postmeta

Remove Orphaned Metadata

#!/bin/bash
# clean-orphaned-metadata.sh

echo "Cleaning orphaned metadata..."

# Orphaned postmeta
ORPHANED_POSTMETA=$(wp db query "
  SELECT COUNT(*) 
  FROM wp_postmeta pm 
  LEFT JOIN wp_posts p ON pm.post_id = p.ID 
  WHERE p.ID IS NULL;
" --skip-column-names)

echo "Orphaned postmeta entries: $ORPHANED_POSTMETA"

if [ "$ORPHANED_POSTMETA" -gt 0 ]; then
    wp db query "
      DELETE pm 
      FROM wp_postmeta pm 
      LEFT JOIN wp_posts p ON pm.post_id = p.ID 
      WHERE p.ID IS NULL;
    "
    echo "✓ Orphaned postmeta cleaned"
fi

# Orphaned commentmeta
ORPHANED_COMMENTMETA=$(wp db query "
  SELECT COUNT(*) 
  FROM wp_commentmeta cm 
  LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID 
  WHERE c.comment_ID IS NULL;
" --skip-column-names)

echo "Orphaned commentmeta entries: $ORPHANED_COMMENTMETA"

if [ "$ORPHANED_COMMENTMETA" -gt 0 ]; then
    wp db query "
      DELETE cm 
      FROM wp_commentmeta cm 
      LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID 
      WHERE c.comment_ID IS NULL;
    "
    echo "✓ Orphaned commentmeta cleaned"
fi

# Orphaned usermeta
ORPHANED_USERMETA=$(wp db query "
  SELECT COUNT(*) 
  FROM wp_usermeta um 
  LEFT JOIN wp_users u ON um.user_id = u.ID 
  WHERE u.ID IS NULL;
" --skip-column-names)

echo "Orphaned usermeta entries: $ORPHANED_USERMETA"

if [ "$ORPHANED_USERMETA" -gt 0 ]; then
    wp db query "
      DELETE um 
      FROM wp_usermeta um 
      LEFT JOIN wp_users u ON um.user_id = u.ID 
      WHERE u.ID IS NULL;
    "
    echo "✓ Orphaned usermeta cleaned"
fi

echo "Orphaned metadata cleanup complete!"

Comprehensive Maintenance Script

#!/bin/bash
# comprehensive-db-maintenance.sh

echo "========================================="
echo "  WordPress Database Maintenance"
echo "========================================="
echo "Started: $(date)"
echo ""

# Configuration
BACKUP_DIR="/backups/wordpress"
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# 1. BACKUP
echo "1. Creating backup..."
BACKUP_FILE="$BACKUP_DIR/maintenance-backup-$(date +%Y%m%d-%H%M%S).sql.gz"
wp db export "$BACKUP_FILE" --add-drop-table --single-transaction
echo "✓ Backup created: $BACKUP_FILE"
echo ""

# 2. DATABASE SIZE (BEFORE)
echo "2. Database size analysis..."
SIZE_BEFORE=$(wp db query "
  SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE();
" --skip-column-names)
echo "Database size: ${SIZE_BEFORE}MB"
echo ""

# 3. CHECK DATABASE
echo "3. Checking database integrity..."
if wp db check > /dev/null 2>&1; then
    echo "✓ Database check passed"
else
    echo "⚠ Database issues detected - running repair..."
    wp db repair
fi
echo ""

# 4. CLEAN REVISIONS
echo "4. Cleaning post revisions..."
REVISIONS=$(wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type='revision';" --skip-column-names)
if [ "$REVISIONS" -gt 0 ]; then
    echo "Deleting $REVISIONS revisions..."
    wp db query "DELETE FROM wp_posts WHERE post_type='revision';"
    echo "✓ Revisions cleaned"
else
    echo "No revisions to clean"
fi
echo ""

# 5. CLEAN TRANSIENTS
echo "5. Cleaning expired transients..."
wp transient delete --expired
echo "✓ Transients cleaned"
echo ""

# 6. CLEAN SPAM COMMENTS
echo "6. Cleaning spam comments..."
SPAM_COUNT=$(wp comment list --status=spam --format=count)
if [ "$SPAM_COUNT" -gt 0 ]; then
    echo "Deleting $SPAM_COUNT spam comments..."
    wp comment delete $(wp comment list --status=spam --format=ids)
    echo "✓ Spam comments cleaned"
else
    echo "No spam comments to clean"
fi
echo ""

# 7. CLEAN TRASHED POSTS
echo "7. Cleaning trashed posts..."
TRASH_COUNT=$(wp post list --post_status=trash --format=count)
if [ "$TRASH_COUNT" -gt 0 ]; then
    echo "Deleting $TRASH_COUNT trashed posts..."
    wp post delete $(wp post list --post_status=trash --format=ids) --force
    echo "✓ Trashed posts cleaned"
else
    echo "No trashed posts to clean"
fi
echo ""

# 8. CLEAN ORPHANED METADATA
echo "8. Cleaning orphaned metadata..."
# Orphaned postmeta
wp db query "
  DELETE pm 
  FROM wp_postmeta pm 
  LEFT JOIN wp_posts p ON pm.post_id = p.ID 
  WHERE p.ID IS NULL;
" 2>/dev/null
# Orphaned commentmeta
wp db query "
  DELETE cm 
  FROM wp_commentmeta cm 
  LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID 
  WHERE c.comment_ID IS NULL;
" 2>/dev/null
echo "✓ Orphaned metadata cleaned"
echo ""

# 9. OPTIMIZE DATABASE
echo "9. Optimizing database..."
wp db optimize
echo "✓ Database optimized"
echo ""

# 10. DATABASE SIZE (AFTER)
echo "10. Final database size..."
SIZE_AFTER=$(wp db query "
  SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE();
" --skip-column-names)
echo "Database size: ${SIZE_AFTER}MB"

# Calculate savings
SAVED=$(echo "$SIZE_BEFORE - $SIZE_AFTER" | bc)
echo "Space reclaimed: ${SAVED}MB"
echo ""

# 11. CLEANUP OLD BACKUPS
echo "11. Cleaning old backups..."
find $BACKUP_DIR -name "maintenance-backup-*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "✓ Old backups cleaned (retention: $RETENTION_DAYS days)"
echo ""

echo "========================================="
echo "  Maintenance Complete!"
echo "========================================="
echo "Finished: $(date)"

Add to cron (weekly on Sunday at 2 AM):

0 2 * * 0 /path/to/comprehensive-db-maintenance.sh >> /var/log/wp-db-maintenance.log 2>&1

Performance Monitoring

Database Size Tracking

#!/bin/bash
# track-db-size.sh

LOG_FILE="/var/log/wp-db-size.log"

SIZE=$(wp db query "
  SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE();
" --skip-column-names)

echo "$(date +%Y-%m-%d\ %H:%M:%S),$SIZE" >> $LOG_FILE

echo "Current database size: ${SIZE}MB"

Add to cron (daily):

0 0 * * * /path/to/track-db-size.sh

Table Size Analysis

#!/bin/bash
# analyze-table-sizes.sh

echo "=== Database Table Size Analysis ==="
echo ""

wp db query "
  SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    table_rows AS 'Rows',
    ROUND(((data_length + index_length) / table_rows), 2) AS 'Avg Row Size (bytes)'
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE()
  ORDER BY (data_length + index_length) DESC;
"

Best Practices

1. Regular Maintenance Schedule

# Daily: Check database health
0 3 * * * wp db check

# Weekly: Optimize database
0 2 * * 0 wp db optimize

# Monthly: Comprehensive maintenance
0 1 1 * * /path/to/comprehensive-db-maintenance.sh

2. Always Backup Before Maintenance

# Before any maintenance operation
wp db export pre-maintenance-$(date +%Y%m%d).sql

3. Monitor Database Growth

# Track size over time
wp db query "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) FROM information_schema.TABLES WHERE table_schema = DATABASE();"

4. Clean Regularly

# Weekly cleanup routine
wp transient delete --expired
wp comment delete $(wp comment list --status=spam --format=ids)

5. Test on Staging First

# Test maintenance scripts on staging before production
# Clone production to staging
# Run maintenance
# Verify results
# Then apply to production

Quick Reference

Essential Commands

# Check database
wp db check                                 # Check all tables
wp db check wp_posts wp_postmeta            # Check specific tables

# Repair database
wp db repair                                # Repair all tables
wp db repair wp_posts                       # Repair specific table

# Optimize database
wp db optimize                              # Optimize all tables
wp db optimize wp_posts wp_postmeta         # Optimize specific tables

# Clean data
wp transient delete --expired               # Delete expired transients
wp comment delete $(wp comment list --status=spam --format=ids)  # Delete spam
wp post delete $(wp post list --post_status=trash --format=ids) --force  # Delete trash

# Analysis
wp db query "SHOW TABLE STATUS;"            # Table statistics
wp db tables                                # List all tables

Troubleshooting

Repair Fails

# Try MySQL repair directly
wp db cli --execute="REPAIR TABLE wp_posts;"

# Check MySQL error log
tail -f /var/log/mysql/error.log

# Verify table engine
wp db query "SHOW TABLE STATUS WHERE Name='wp_posts';"

Optimization Takes Too Long

# Optimize tables individually
for table in $(wp db tables); do
  echo "Optimizing $table..."
  wp db optimize "$table"
done

# Or optimize only large tables
wp db query "
  SELECT table_name 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE() 
  AND data_length > 10485760;
" --skip-column-names | xargs wp db optimize

Summary

Operation	Command	Frequency	Purpose
Check	`wp db check`	Daily	Detect errors
Repair	`wp db repair`	As needed	Fix corruption
Optimize	`wp db optimize`	Weekly	Improve performance
Clean Revisions	Custom SQL	Monthly	Reduce bloat
Clean Transients	`wp transient delete --expired`	Weekly	Remove expired data
Clean Spam	`wp comment delete`	Weekly	Remove spam

Key Takeaway

Regular database maintenance is essential for performance, reliability, and site health. Automate checks, repairs, and optimization with cron jobs for hands-free maintenance.

Next Steps

Master database operations: Database Operations
Learn search-replace: Search & Replace
Explore user management: User Management

Understanding Database Maintenance​

Database Checking​

Check All Tables​

Check Specific Tables​

Automated Health Checks​

Database Repair​

Repair All Tables​

Repair Specific Tables​

Emergency Repair Workflow​

Repair with MySQL Directly​

Database Optimization​

Optimize All Tables​

Optimize Specific Tables​

Scheduled Optimization​

Cleaning Database Bloat​

Remove Post Revisions​

Clean Transients​

Remove Spam Comments​

Clean Trashed Posts​

Remove Orphaned Metadata​

Comprehensive Maintenance Script​

Performance Monitoring​

Database Size Tracking​

Table Size Analysis​

Best Practices​

1. Regular Maintenance Schedule​

2. Always Backup Before Maintenance​

3. Monitor Database Growth​

4. Clean Regularly​

5. Test on Staging First​

Quick Reference​

Essential Commands​

Troubleshooting​

Repair Fails​

Optimization Takes Too Long​

Summary​

Next Steps​