WordPress Database Optimization 2026: WP-Optimize, slow queries, indexing, revisions cleanup

The WordPress database — rarely thought about until the site slows down for no apparent reason. Yet it's often the real bottleneck, not the PHP code, the server, or the cache.

A wp_posts table with 400,000 rows, 350,000 of which are revisions. A wp_options table weighing 200 MB stuffed with expired transients. Missing indexes turning a simple menu query into a 10-second table scan. We see this every day.

This guide gives you the keys to diagnose, clean, and optimize your WordPress database in 2026 — with tools, metrics, and tested procedures.

Why the database slows down

The problem isn't WordPress itself — it's what accumulates over time.

The 5 main sources of bloat

---

Diagnosing your database health

Method 1 — phpMyAdmin (direct access)

1. Log into phpMyAdmin (or Adminer)
2. Select the WordPress database
3. Check the Size column — identify the biggest tables
4. Sort by Rows to see the most populated tables

Method 2 — Query Monitor

1. Install Query Monitor
2. Open the toolbar → Queries by Component tab
3. Spot plugins making the most queries
4. Filter by SELECT to find slow queries (over 0.05 s)

Method 3 — MySQL Slow Query Log

In my.cnf or my.ini:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2

Analyze slow queries with mysqldumpslow:

mysqldumpslow -t 10 /var/log/mysql/slow-queries.log

Key metrics to monitor

---

Cleaning up post revisions

Why it matters

Every edit of a post or page saves a revision. On a site with 10 active authors and 500 posts, you can reach 50,000 revisions in a year. These revisions are stored in wp_posts with the type revision.

Cleanup via WP-CLI (recommended)

# Count revisions
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';"

# Delete all revisions
wp db query "DELETE FROM wp_posts WHERE post_type = 'revision';"

# Clean orphaned postmeta
wp db query "DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;"

Cleanup via WP-Optimize

1. Install WP-Optimize
2. Go to WP-Optimize → Database Cleanup
3. Check Purge all revisions (or keep the last N)
4. Run the optimization
5. Schedule automatic weekly cleanup

Limiting revisions going forward

Add to wp-config.php:

define('WP_POST_REVISIONS', 5);

This limits revisions to 5 per post. Recommended value: between 3 and 10 depending on editorial activity.

---

Cleaning up transients

What is a transient

Transients are temporary cached data stored in wp_options. Problem: many expire without being deleted, especially on sites with poorly coded themes or plugins.

Cleanup via WP-CLI

# Count transients
wp transient list --expired --count

# Delete all expired transients
wp transient delete --expired

Cleanup via direct MySQL

DELETE FROM wp_options
WHERE option_name LIKE '%\_transient\_%'
  AND option_name NOT LIKE '%\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

Automatic cleanup with WP-Optimize

WP-Optimize includes a Clean expired transients option — enable it and schedule it.

---

MySQL indexing optimization

Why indexes are critical

Without an index, MySQL scans the entire table to find a row. With an index, the search is nearly instant.

Check for missing indexes

Use this snippet in phpMyAdmin or WP-CLI:

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_wordpress_database';

Recommended indexes for WordPress

Adding an index via WP-CLI

wp db query "ALTER TABLE wp_postmeta ADD INDEX post_meta (post_id, meta_key);"

Before/after indexing comparison

---

Using WP-Optimize as your central tool

WP-Optimize is our recommended tool for WordPress DB cleanup in 2026.

Key features

Recommended cleanup schedule

• Daily: expired transients, comment spam
• Weekly: revisions (if limited upstream), table optimization
• Monthly: orphaned meta, DB size audit

WP-Optimize alternatives

---

Optimization methods comparison

---

What we take away

WordPress database optimization isn't a one-time operation — it's a maintenance routine.

Start by diagnosing: total size, number of revisions, expired transients. Query Monitor is your best friend for that.

Take action: WP-Optimize for routine cleanup, WP-CLI for heavy operations, MySQL for advanced indexing.

Automate: a scheduled weekly cleanup beats a big annual purge.

Secure: always back up the database before any cleanup or indexing operation.

:::callout-warning

Never run OPTIMIZE TABLE on an InnoDB database over 1 GB without a maintenance window. The operation locks the table for several minutes — your site will be read-only during that time.

:::

---

Article updated July 9, 2026. Sources: MySQL documentation, Volade field tests (200+ sites analyzed), WP-Optimize and Query Monitor feedback.