{"slug":"wordpress-database-optimization","title":"WordPress Database Optimization","tags":["database","mysql","performance","cleanup","revisions"],"agent_summary":"WordPress database bloat identification via SQL queries, cleanup SQL for revisions, orphaned postmeta, expired transients, spam comments, WP-CLI database commands, preventing future bloat with revision limits, and WP-Optimize scheduled cleanup.","trigger_phrases":["wordpress database optimization","wordpress db cleanup","delete post revisions sql","orphaned postmeta wordpress","optimize wordpress database","wp-optimize","wordpress database bloat","mysql wordpress"],"runnable":true,"markdown":"\nWordPress databases bloat with revisions, transients, spam, orphaned metadata, and sessions. Run cleanup monthly on active sites.\n\n## Identify Bloat\n\n```sql\n-- Table sizes\nSELECT table_name,\n ROUND(data_length/1024/1024, 2) AS 'Data MB',\n ROUND(index_length/1024/1024, 2) AS 'Index MB'\nFROM information_schema.tables\nWHERE table_schema = 'your_database_name'\nORDER BY data_length DESC;\n\n-- Count post revisions\nSELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';\n\n-- Count orphaned postmeta (no matching post)\nSELECT COUNT(*) FROM wp_postmeta pm\nLEFT JOIN wp_posts p ON pm.post_id = p.ID\nWHERE p.ID IS NULL;\n\n-- Count expired transients\nSELECT COUNT(*) FROM wp_options\nWHERE option_name LIKE '_transient_timeout_%'\nAND option_value < UNIX_TIMESTAMP();\n```\n\n## Cleanup SQL (phpMyAdmin)\n\n```sql\n-- Delete all post revisions\nDELETE FROM wp_posts WHERE post_type = 'revision';\n\n-- Delete orphaned postmeta\nDELETE pm FROM wp_postmeta pm\nLEFT JOIN wp_posts wp ON wp.ID = pm.post_id\nWHERE wp.ID IS NULL;\n\n-- Delete expired transients\nDELETE FROM wp_options\nWHERE option_name LIKE '_transient_%'\nAND option_name LIKE '%_transient_timeout_%'\nAND option_value < UNIX_TIMESTAMP();\n\n-- Delete spam and trash comments\nDELETE FROM wp_comments WHERE comment_approved = 'spam';\nDELETE FROM wp_comments WHERE comment_approved = 'trash';\nDELETE FROM wp_commentmeta\nWHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);\n\n-- Optimize tables (defragments, updates statistics)\nOPTIMIZE TABLE wp_options, wp_posts, wp_postmeta, wp_comments;\n```\n\n## WP-CLI Database Cleanup\n\n```bash\n# Delete all revisions\nwp post delete $(wp post list --post_type=revision --format=ids) --force\n\n# Delete expired transients\nwp transient delete --all --expired\n\n# Optimize all database tables\nwp db optimize\n\n# Check for table errors\nwp db check\n\n# Full database size report\nwp db size\n```\n\n## Prevent Future Bloat\n\n```php\n// Limit post revisions (in wp-config.php)\ndefine('WP_POST_REVISIONS', 3);\n\n// Or per post type (in functions.php)\nadd_filter('wp_revisions_to_keep', function($num, $post) {\n if (in_array($post->post_type, ['product', 'service'])) return 3;\n return $num;\n}, 10, 2);\n\n// Increase autosave interval (default 60s — reduce revision count)\ndefine('AUTOSAVE_INTERVAL', 300); // 5 minutes\n```\n\n## Automated Cleanup with WP-Optimize\n\n1. Install WP-Optimize\n2. Settings > Scheduler: Run weekly\n3. Enable: Remove post revisions, expired transients, spam comments, orphaned data\n4. Run optimization after cleanup (defragments tables)\n5. Schedule: Monday at 3 AM (after MainWP update pipeline)\n","html":"

WordPress databases bloat with revisions, transients, spam, orphaned metadata, and sessions. Run cleanup monthly on active sites.

\n

Identify Bloat

\n
-- Table sizes\nSELECT table_name,\n       ROUND(data_length/1024/1024, 2) AS 'Data MB',\n       ROUND(index_length/1024/1024, 2) AS 'Index MB'\nFROM information_schema.tables\nWHERE table_schema = 'your_database_name'\nORDER BY data_length DESC;\n\n-- Count post revisions\nSELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';\n\n-- Count orphaned postmeta (no matching post)\nSELECT COUNT(*) FROM wp_postmeta pm\nLEFT JOIN wp_posts p ON pm.post_id = p.ID\nWHERE p.ID IS NULL;\n\n-- Count expired transients\nSELECT COUNT(*) FROM wp_options\nWHERE option_name LIKE '_transient_timeout_%'\nAND option_value < UNIX_TIMESTAMP();\n
\n

Cleanup SQL (phpMyAdmin)

\n
-- Delete all post revisions\nDELETE FROM wp_posts WHERE post_type = 'revision';\n\n-- Delete orphaned postmeta\nDELETE pm FROM wp_postmeta pm\nLEFT JOIN wp_posts wp ON wp.ID = pm.post_id\nWHERE wp.ID IS NULL;\n\n-- Delete expired transients\nDELETE FROM wp_options\nWHERE option_name LIKE '_transient_%'\nAND option_name LIKE '%_transient_timeout_%'\nAND option_value < UNIX_TIMESTAMP();\n\n-- Delete spam and trash comments\nDELETE FROM wp_comments WHERE comment_approved = 'spam';\nDELETE FROM wp_comments WHERE comment_approved = 'trash';\nDELETE FROM wp_commentmeta\nWHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);\n\n-- Optimize tables (defragments, updates statistics)\nOPTIMIZE TABLE wp_options, wp_posts, wp_postmeta, wp_comments;\n
\n

WP-CLI Database Cleanup

\n
# Delete all revisions\nwp post delete $(wp post list --post_type=revision --format=ids) --force\n\n# Delete expired transients\nwp transient delete --all --expired\n\n# Optimize all database tables\nwp db optimize\n\n# Check for table errors\nwp db check\n\n# Full database size report\nwp db size\n
\n

Prevent Future Bloat

\n
// Limit post revisions (in wp-config.php)\ndefine('WP_POST_REVISIONS', 3);\n\n// Or per post type (in functions.php)\nadd_filter('wp_revisions_to_keep', function($num, $post) {\n    if (in_array($post->post_type, ['product', 'service'])) return 3;\n    return $num;\n}, 10, 2);\n\n// Increase autosave interval (default 60s — reduce revision count)\ndefine('AUTOSAVE_INTERVAL', 300); // 5 minutes\n
\n

Automated Cleanup with WP-Optimize

\n
    \n
  1. Install WP-Optimize
    2. \n
  2. Settings > Scheduler: Run weekly
    3. \n
  3. Enable: Remove post revisions, expired transients, spam comments, orphaned data
    4. \n
  4. Run optimization after cleanup (defragments tables)
    5. \n
  5. Schedule: Monday at 3 AM (after MainWP update pipeline)
    6. \n
\n"}