Docs/Performance/Database Optimization

Database Optimization

Comprehensive database cleanup, optimization, and maintenance for peak WordPress performance

2025 Edition

Overview

ProRank SEO's Database Optimization provides enterprise-grade tools to clean, optimize, and maintain your WordPress database. Remove unnecessary data, optimize table structures, manage options and cron jobs, and keep your site running at peak performance with automated maintenance and intelligent cleanup algorithms.

50% Smaller

Reduce database size dramatically with intelligent cleanup

40% Faster

Improve query performance with optimized tables

Safe Cleanup

Automatic backups before any database operations

Enterprise Features

Database Optimization includes advanced features like orphaned data detection, intelligent table optimization, autoload management, and comprehensive backup/restore capabilities.

Quick Start

Access Database Optimization

Navigate to ProRank SEO → Performance → Database & Server

Create Backup

Always create a backup before operations (automatic by default)

Run Safe Cleanup

Start with spam comments, expired transients, and orphaned data

Optimize Tables

Run table optimization to reclaim space and improve performance

Database Cleanup

Posts & Pages

Post revisions (keep configurable days)
Auto-drafts and abandoned drafts
Trashed posts and pages

Comments

Spam comments (always safe)
Trashed and pending comments
Pingbacks and trackbacks

Orphaned Data

Post, comment, user, and term metadata
Broken term relationships
Abandoned plugin/theme data

Transients

Expired transients (always safe)
All transients (for troubleshooting)
Abandoned timeout records

Table Management

Table Operations

Optimize

Reclaim space, rebuild indexes, improve query performance

Repair

Fix corrupted tables, resolve crashes, restore integrity

Convert

Upgrade MyISAM to InnoDB for better performance

Smart Detection

Tables with overhead >10MB are automatically flagged for optimization. Core WordPress tables are protected and marked for identification.

Options Management

Autoload Optimization

WordPress loads all autoloaded options on every page request. Keep this under 1MB for optimal performance.

Should Autoload

Core site settings
Active theme options
Essential plugin settings

Should NOT Autoload

Large cache data
Analytics/statistics
Backup configurations

Cron Jobs Management

Manage WordPress scheduled tasks, remove orphaned jobs from deleted plugins, and fix scheduling conflicts.

Find Orphaned

Detect jobs from removed plugins

Remove Stuck

Clear jobs that never complete

Fix Schedule

Resolve duplicate job issues

Backup & Restore

Automatic Backups

Created before any major operation to ensure safety

• Before cleanup operations
• Before table optimization
• Before bulk deletions
• 7-day retention (configurable)

Easy Restore

One-click restoration from any backup point

• Preview before restore
• Selective restoration
• Verify integrity
• Rollback capability

Important

Always verify your backup before performing major cleanup operations. While automatic backups are created, having an external backup provides additional safety.

Configuration

Retention Settings

// Conservative (Production Sites)
{
  retention_days: 60,           // Keep 2 months
  auto_cleanup: 'weekly',       // Weekly schedule
  backup_before_cleanup: true,  // Always backup
  confirmation_required: true   // Confirm actions
}

// Balanced (Most Sites)
{
  retention_days: 30,          // Keep 30 days
  auto_cleanup: 'weekly',      // Weekly schedule
  backup_before_cleanup: true,
  confirmation_required: false
}

// Aggressive (Development)
{
  retention_days: 7,           // Keep 1 week
  auto_cleanup: 'daily',       // Daily cleanup
  backup_before_cleanup: true,
  confirmation_required: false
}

Automation Options

Cleanup Schedule

• Daily (high-traffic sites)
• Weekly (recommended)
• Monthly (low-traffic sites)
• Disabled (manual only)

Cleanup Items

• Safe items only
• Include revisions
• Include drafts
• Full cleanup

Expected Results

30-50%

Database Size Reduction

20-40%

Query Speed Improvement

15-30%

Overall Performance Boost

40-50%

Faster Backup Times

Best Practices

Maintenance Schedule

DAILY

Clean expired transients, remove spam

WEEKLY

Clean revisions, optimize high-traffic tables

MONTHLY

Full cleanup, optimize all tables, review options

Safety Checklist

Backup created (automatic or manual)Low traffic period selectedStaging environment testedKnow restore procedure

Troubleshooting

High database size despite cleanup

Check for large options, plugin data in custom tables, or accumulated logs.

Solution: Review largest tables, check options size, look for log tables

Operation timeout errors

Large operations may exceed PHP execution limits.

Solution: Break into smaller operations, increase PHP timeout, run during low traffic

Cannot delete certain items

May be due to permissions or foreign key constraints.

Solution: Check user permissions, review error logs, try during low-traffic period

CLI Commands

WP-CLI Integration

Advanced users can use WP-CLI commands for automation and scripting (Business/Agency licenses only).

# Run cleanup
wp prorank-db cleanup --type=all

# Optimize specific tables
wp prorank-db optimize --tables=wp_posts,wp_postmeta

# Create backup with label
wp prorank-db backup --label="before-update"

# Show database statistics
wp prorank-db stats

# Clean specific items
wp prorank-db clean revisions --days=30
wp prorank-db clean transients --expired-only

# Restore from backup
wp prorank-db restore --backup="2025-01-20-backup.sql"