Database Optimization

Database Health Matters

A bloated database slows down everything. Regular maintenance is essential.

Common Database Bloat

Cleaning Post Revisions

// Check current revisions
global $wpdb;
$revisions = $wpdb->get_var(
    "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
);
echo "Found {$revisions} revisionsn";

// Delete old revisions (keep last 3)
$wpdb->query("
    DELETE FROM {$wpdb->posts} 
    WHERE post_type = 'revision'
    AND ID NOT IN (
        SELECT * FROM (
            SELECT ID FROM {$wpdb->posts} p2
            WHERE p2.post_type = 'revision'
            ORDER BY p2.post_modified DESC
            LIMIT 3
        ) as subquery
    )
");

// Limit future revisions in wp-config.php
define('WP_POST_REVISIONS', 3);

// Or disable completely
define('WP_POST_REVISIONS', false);

Cleaning Auto-Drafts and Trash

// Delete auto-drafts older than 7 days
$wpdb->query("
    DELETE FROM {$wpdb->posts}
    WHERE post_status = 'auto-draft'
    AND post_date < DATE_SUB(NOW(), INTERVAL 7 DAY)
");

// Empty trash
$wpdb->query("
    DELETE FROM {$wpdb->posts}
    WHERE post_status = 'trash'
");

// Delete orphaned postmeta
$wpdb->query("
    DELETE pm FROM {$wpdb->postmeta} pm
    LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE p.ID IS NULL
");

Optimizing the Options Table

The autoload column is critical:

// Find large autoloaded options
$results = $wpdb->get_results("
    SELECT option_name, LENGTH(option_value) as size
    FROM {$wpdb->options}
    WHERE autoload = 'yes'
    ORDER BY size DESC
    LIMIT 20
");

// Total autoloaded size
$total = $wpdb->get_var("
    SELECT SUM(LENGTH(option_value))
    FROM {$wpdb->options}
    WHERE autoload = 'yes'
");
echo "Autoloaded options: " . number_format($total / 1024, 2) . " KBn";

// Disable autoload for large options
$wpdb->update(
    $wpdb->options,
    ['autoload' => 'no'],
    ['option_name' => 'problematic_option']
);

Cleaning Transients

// Delete expired transients
$wpdb->query("
    DELETE FROM {$wpdb->options}
    WHERE option_name LIKE '_transient_timeout_%'
    AND option_value < UNIX_TIMESTAMP()
");

$wpdb->query("
    DELETE FROM {$wpdb->options}
    WHERE option_name LIKE '_transient_%'
    AND option_name NOT LIKE '_transient_timeout_%'
    AND option_name NOT IN (
        SELECT REPLACE(option_name, '_timeout', '')
        FROM {$wpdb->options}
        WHERE option_name LIKE '_transient_timeout_%'
    )
");

Optimizing Queries

Use Query Monitor

Install Query Monitor plugin to identify slow queries.

Common Query Optimizations

// ❌ Bad: Query all posts then filter
$posts = get_posts(['posts_per_page' => -1]);
$filtered = array_filter($posts, function($p) {
    return get_post_meta($p->ID, 'featured', true);
});

// ✅ Good: Filter in query
$posts = get_posts([
    'meta_key' => 'featured',
    'meta_value' => '1',
    'posts_per_page' => 10,
]);

// ❌ Bad: Multiple queries for meta
foreach ($posts as $post) {
    $author = get_post_meta($post->ID, 'author', true);
    $date = get_post_meta($post->ID, 'event_date', true);
}

// ✅ Good: Single query with caching
update_postmeta_cache(wp_list_pluck($posts, 'ID'));
foreach ($posts as $post) {
    $author = get_post_meta($post->ID, 'author', true);
    $date = get_post_meta($post->ID, 'event_date', true);
}

Adding Database Indexes

-- Check existing indexes
SHOW INDEX FROM wp_postmeta;

-- Add index for commonly queried meta
ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(50));

-- For WooCommerce
ALTER TABLE wp_postmeta ADD INDEX price_index (meta_key, meta_value) 
WHERE meta_key = '_price';

Table Optimization

-- Optimize tables (reclaims space)
OPTIMIZE TABLE tmpf3bb4d_posts, wp_postmeta, wp_options, wp_comments;

-- Analyze tables (updates statistics)
ANALYZE TABLE tmpf3bb4d_posts, wp_postmeta, wp_options;

-- Check for issues
CHECK TABLE tmpf3bb4d_posts, wp_postmeta;

// Schedule weekly optimization
if (!wp_next_scheduled('weekly_db_optimize')) {
    wp_schedule_event(time(), 'weekly', 'weekly_db_optimize');
}

add_action('weekly_db_optimize', function() {
    global $wpdb;
    $tables = $wpdb->get_col("SHOW TABLES LIKE '{$wpdb->prefix}%'");
    foreach ($tables as $table) {
        $wpdb->query("OPTIMIZE TABLE {$table}");
    }
});

Using WP-CLI for Cleanup

# Delete revisions
wp post delete $(wp post list --post_type='revision' --format=ids)

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

# Delete transients
wp transient delete --expired

# Optimize database
wp db optimize

# Repair database
wp db repair

# Search-replace (careful!)
wp search-replace 'old-domain.com' 'new-domain.com' --dry-run

Recommended Maintenance Schedule

Next Steps

In the next lesson, we'll optimize CSS and JavaScript delivery.