WordPress Database Optimization: Speed Up Your Queries

Every WordPress page load involves dozens of database queries. On a well-maintained site these are fast. On a site with years of accumulated revisions, transients, spam, and orphaned data, they slow down noticeably. Database optimization is one of the highest-impact backend performance improvements.

What causes database bloat?

• Post revisions: WordPress saves a revision every time you save a draft. Active bloggers can accumulate thousands of revision records.
• Spam comments: Akismet catches spam but doesn't delete it automatically.
• Transients: Temporary data stored by plugins. Many are never cleaned up.
• Deleted posts in trash: Trashed posts remain in the database.
• Orphaned meta data: Post meta, user meta, and term meta left behind by deleted posts or plugins.
• WooCommerce session data: Old shopping sessions accumulate over time.
• Auto drafts: WordPress automatically saves drafts frequently.

Step 1: Limit post revisions

Add to wp-config.php to limit revisions going forward:

// Keep only last 3 revisions
define('WP_POST_REVISIONS', 3);

To delete existing excess revisions, use a plugin like WP-Sweep or run this SQL query (backup first!):

DELETE FROM wp_posts WHERE post_type = 'revision';

Step 2: Clean expired transients

Transients are temporary plugin data stored in the wp_options table. Many expire but aren't cleaned up:

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

Or use a plugin: Transient Cleaner, WP-Optimize, or WP-Sweep handle this safely.

Step 3: Clean spam and trash

In WordPress admin: Comments → All Comments → Spam → Empty Spam. Posts → Trash → Empty Trash. Do this monthly.

Step 4: Optimize MySQL tables

After deleting data, MySQL tables have fragmented space. Optimize them via phpMyAdmin (select all tables → Optimize) or SQL:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

This reclaims disk space and improves query performance.

Step 5: Add database indexes (advanced)

The wp_options table's autoload column is queried on every page load. Adding an index improves performance on large sites:

ALTER TABLE wp_options ADD INDEX autoload_idx (autoload);

Recommended plugins

• WP-Optimize — Comprehensive cleanup + optimization with scheduled automation
• WP-Sweep — Safe, granular cleanup with preview before deletion
• Advanced Database Cleaner — Best for orphaned data cleanup

Enable Query Monitor for debugging

The Query Monitor plugin shows every database query on each page load, including execution time and the code that called it. This is invaluable for finding slow queries caused by plugins or custom code.