How to Optimize Your WordPress Database

wp plugin install wp-optimize --activate

DELETE FROM wp_posts WHERE post_type = 'revision';

-- Also clean orphaned meta
DELETE FROM wp_postmeta 
WHERE post_id NOT IN (SELECT ID FROM wp_posts);

DELETE FROM wp_options 
WHERE option_name LIKE '%_transient_%' 
AND option_name NOT LIKE '%_transient_timeout_%';

DELETE FROM wp_options 
WHERE option_name LIKE '%_transient_timeout_%' 
AND option_value < UNIX_TIMESTAMP();

DELETE FROM wp_woocommerce_sessions 
WHERE session_expiry < UNIX_TIMESTAMP();

DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';

-- Clean orphaned comment meta
DELETE FROM wp_commentmeta 
WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

-- Run in phpMyAdmin
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, 
wp_comments, wp_commentmeta, wp_terms, wp_termmeta;

# Optimize all tables
wp db optimize

# Repair tables
wp db repair

# Check database size
wp db size --tables

# Delete all transients
wp transient delete --all

# Delete expired transients only
wp transient delete --expired

# Delete all post 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 revisions older than 30 days
wp post delete $(wp post list --post_type='revision' --date_query='[{"before":"30 days ago"}]' --format=ids)

# Export before cleanup (safety)
wp db export backup-before-cleanup.sql

SELECT 
  table_name AS 'Table',
  ROUND(data_length / 1024 / 1024, 2) AS 'Data (MB)',
  ROUND(index_length / 1024 / 1024, 2) AS 'Index (MB)',
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;

// Limit post revisions (default: unlimited)
define('WP_POST_REVISIONS', 3);

// Empty trash faster (default: 30 days)
define('EMPTY_TRASH_DAYS', 7);

// Increase autosave interval (default: 60 seconds)
define('AUTOSAVE_INTERVAL', 300);

// Disable post revisions entirely (not recommended)
// define('WP_POST_REVISIONS', false);