WordPress DB Optimization

wp_options Health Check

-- Row count (healthy: 500–2000, bad: 50k+)
SELECT COUNT(*) FROM wp_options;

-- Autoload payload size (>1MB = problem, >3MB = serious, >10MB = broken)
SELECT SUM(LENGTH(option_value)) / 1024 / 1024 AS autoload_mb
FROM wp_options WHERE autoload = 'yes';

-- Biggest rows overall
SELECT option_name,
       LENGTH(option_value) / 1024 AS size_kb,
       autoload
FROM wp_options
ORDER BY LENGTH(option_value) DESC
LIMIT 30;

-- Biggest autoloaded rows above 100KB
SELECT option_name,
       LENGTH(option_value) / 1024 AS size_kb
FROM wp_options
WHERE autoload = 'yes'
AND LENGTH(option_value) > 102400
ORDER BY LENGTH(option_value) DESC;

Transients

-- Count expired transients still in DB
SELECT COUNT(*) FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();

# Delete expired only (safe for production)
wp transient delete --expired --path=/var/www/html

# Delete ALL transients (only if Redis is active)
wp transient delete --all --path=/var/www/html

Warning

Only run --all if Redis object cache is confirmed working.
Without Redis, the next traffic wave will hammer the DB as everything recalculates.

Autoload Fixes

-- Disable autoload on known-safe computed/cache rows
UPDATE wp_options SET autoload = 'no'
WHERE option_name = 'elementor_css_print_method';

UPDATE wp_options SET autoload = 'no'
WHERE option_name LIKE 'learndash_course_info_%';

UPDATE wp_options SET autoload = 'no'
WHERE option_name LIKE 'bb_platform_%'
AND option_name NOT IN ('bb_platform_core', 'bb_platform_settings');

Warning

Never blindly mass-set autoload = 'no'. Options like siteurl, blogname,
active_plugins must stay autoloaded or the site crashes.

Orphaned Data Cleanup

-- Orphaned postmeta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- Orphaned usermeta
DELETE um FROM wp_usermeta um
LEFT JOIN wp_users u ON um.user_id = u.ID
WHERE u.ID IS NULL;

-- Old BuddyBoss activity (discuss retention period with client first)
DELETE FROM wp_activity
WHERE date_recorded < DATE_SUB(NOW(), INTERVAL 1 YEAR);

# Delete post revisions
wp post delete $(wp post list --post_type=revision --format=ids) \
  --force --path=/var/www/html

Table Fragmentation

-- Check fragmentation across key tables
SELECT table_name,
       data_length / 1024 / 1024 AS data_mb,
       data_free / 1024 / 1024 AS free_mb,
       ROUND(data_free / (data_length + data_free) * 100, 1) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name IN ('wp_options', 'wp_postmeta', 'wp_usermeta');

-- Reclaim space after bulk deletes
OPTIMIZE TABLE wp_options;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_usermeta;

Warning

OPTIMIZE TABLE on large InnoDB tables can take minutes and causes a lock.
Run during off-peak or use pt-online-schema-change for zero-downtime.
Requires ~2x table size in free disk space temporarily.

Orphaned Plugin Data Detection

-- Find high-volume prefixes (cross-ref against active plugins)
SELECT DISTINCT SUBSTRING_INDEX(option_name, '_', 1) AS prefix,
       COUNT(*) AS count,
       SUM(LENGTH(option_value)) / 1024 AS total_kb
FROM wp_options
GROUP BY prefix
ORDER BY total_kb DESC
LIMIT 40;

Redis Verification

redis-cli ping                          # expect PONG
redis-cli info stats | grep keyspace    # hits vs misses ratio
wp redis status --path=/var/www/html    # plugin connection status

# Watch BuddyPress cache writes in real time
redis-cli monitor | grep buddypress

Query Monitor (Temporary — Never Leave on Production)

wp plugin install query-monitor --activate --path=/var/www/html
# Load activity feed → check query count
# 50+ queries with near-identical patterns = N+1 confirmed
wp plugin deactivate query-monitor --path=/var/www/html

Slow Query Log

-- Enable at runtime
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Disable when done
SET GLOBAL slow_query_log = 'OFF';
SET GLOBAL log_queries_not_using_indexes = 'OFF';

# /etc/mysql/mysql.conf.d/mysqld.cnf (permanent)
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

tail -f /var/log/mysql/slow.log
# Red flag: Rows_examined in the hundreds of thousands for small Rows_sent

wp_bp_activity Index Check

SHOW INDEX FROM wp_bp_activity;
SHOW INDEX FROM wp_bp_activity_meta;

-- Verify query uses index (look for type=ALL = full table scan = bad)
EXPLAIN SELECT * FROM wp_bp_activity
WHERE component = 'activity'
ORDER BY date_recorded DESC LIMIT 20;

Automated Weekly Cleanup Script

#!/bin/bash
# /usr/local/bin/wp-db-cleanup.sh

WP_PATH="/var/www/html"
LOG="/var/log/wp-cleanup.log"
DATE=$(date '+%Y-%m-%d %H:%M')
DB_NAME="wp_database"

echo "[$DATE] Starting cleanup" >> $LOG

# Backup wp_options before touching anything
mysqldump $DB_NAME wp_options > /backups/wp_options_$(date +%Y%m%d).sql

# Cleanup
wp transient delete --expired --path=$WP_PATH >> $LOG 2>&1
wp post delete $(wp post list --post_type=revision --format=ids --path=$WP_PATH) \
  --force --path=$WP_PATH >> $LOG 2>&1
wp cache flush --path=$WP_PATH >> $LOG 2>&1

# Report autoload payload size
AUTOLOAD_SIZE=$(wp eval \
  'echo round(strlen(serialize(wp_load_alloptions())) / 1024, 1);' \
  --path=$WP_PATH 2>/dev/null)
echo "[$DATE] Autoload payload: ${AUTOLOAD_SIZE}KB" >> $LOG

echo "[$DATE] Done" >> $LOG

# Add to crontab — weekly Sunday 3AM
0 3 * * 0 /usr/local/bin/wp-db-cleanup.sh

PHP Settings for LearnDash + BuddyBoss

memory_limit = 512M
max_execution_time = 300
upload_max_filesize = 256M
max_input_vars = 5000

Note

max_input_vars below 3000 causes LearnDash settings page to silently drop fields.

Quick Reference — What's Safe to Delete

Autoload Size Thresholds