WordPress Database Optimization: How to Clean and Speed Up Your Database

Every page load, form submission, plugin operation, and user session in WordPress involves database queries. Over time, the database accumulates overhead: old post revisions, expired transients, orphaned metadata, spam comments, and unused tables from deactivated plugins. This bloat slows down queries, increases backup sizes, and degrades overall site performance. This guide covers how to identify database bloat, clean it up safely, and prevent it from returning.

What WordPress Stores in the Database

Understanding what lives in your database helps you identify what can be safely removed. A standard WordPress installation uses 12 core tables, and plugins add their own tables as needed.

Common Sources of Database Bloat

Post Revisions

WordPress saves a revision every time you click "Save Draft" or "Update" on a post or page. A post edited 50 times has 50 revisions stored in the wp_posts table, each with its own set of postmeta entries. On a site with hundreds of posts, revisions can account for thousands of unnecessary database rows.

Transients

Transients are temporary data stored in the wp_options table by WordPress core and plugins. They have expiration times, but WordPress does not automatically delete expired transients. They accumulate over time and can grow the options table significantly, especially on sites with many plugins.

Orphaned Data

When you delete a post, its metadata (wp_postmeta entries) may not be deleted. Similarly, deleting comments, users, or terms can leave behind orphaned metadata rows. Deactivated and deleted plugins often leave their database tables behind, consuming space and cluttering your database.

Spam and Trashed Content

Spam comments, trashed posts, and trashed comments sit in the database until manually emptied. Sites with Akismet or other spam filters may accumulate thousands of spam comments that need periodic cleanup. For related information, see our guide on WooCommerce Checkout Optimization: Reduce Cart Aba.

Auto-Drafts

WordPress creates auto-draft posts when you click "Add New" in the post editor, even if you never publish. These accumulate over time and serve no purpose after the editing session ends.

Manual Database Cleanup via SQL

For those comfortable with SQL, you can clean up directly using phpMyAdmin or a database client. Always back up your database before running cleanup queries.

Delete Post Revisions

-- Delete all post revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Delete orphaned postmeta (metadata for deleted posts)
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Clean Up Transients

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

-- Delete the transient data for expired transients
DELETE FROM wp_options
WHERE option_name LIKE '%_transient_%'
AND option_name NOT LIKE '%_transient_timeout_%';

Remove Spam and Trashed Comments

-- Delete spam comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- Delete trashed comments
DELETE FROM wp_comments WHERE comment_approved = 'trash';

-- Delete orphaned comment metadata
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;

Plugin-Based Database Cleanup

If you prefer a GUI-based approach, several plugins handle database optimization with scheduling capabilities:

WP-Optimize

WP-Optimize is a free plugin that cleans revisions, drafts, spam, transients, and orphaned data. It also optimizes database tables and supports scheduled cleanups. The Pro version adds multisite support and advanced scheduling.

WP Rocket Database Optimization

WP Rocket includes a Database tab that handles revisions, auto-drafts, trashed posts, spam comments, transients, and table optimization. Since WP Rocket is primarily a caching plugin, you get database cleanup alongside performance optimization in one tool.

Advanced Database Cleaner

This plugin goes deeper by detecting tables left behind by deactivated plugins and orphaned cron events. It categorizes tables as WordPress core, plugin-created, or unknown, helping you identify which tables are safe to remove.

wp-config.php Optimizations

Prevent database bloat at the source by adding these constants to your wp-config.php file:

Limit Post Revisions

// Keep only 5 revisions per post (recommended)
define( 'WP_POST_REVISIONS', 5 );

// Disable revisions entirely (not recommended for most sites)
define( 'WP_POST_REVISIONS', false );

Setting revisions to 5 provides enough history to recover from mistakes while preventing unlimited revision growth. For content-heavy sites with frequent edits, 10 revisions is a reasonable alternative.

Auto-Empty Trash

// Empty trash every 7 days (default is 30)
define( 'EMPTY_TRASH_DAYS', 7 );

// Disable trash (items deleted immediately - use with caution)
define( 'EMPTY_TRASH_DAYS', 0 );

Autosave Interval

// Increase autosave interval to 5 minutes (default is 60 seconds)
define( 'AUTOSAVE_INTERVAL', 300 );

Optimizing Database Tables

After deleting rows, run an OPTIMIZE operation to reclaim fragmented space:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;

Schedule this monthly or after large cleanup operations. Most cleanup plugins include table optimization in their scheduled tasks.

Scheduling Automatic Cleanup

Set up automated schedules: weekly transient and spam cleanup, monthly revision removal and table optimization, and quarterly audits of orphaned plugin tables. WP-Optimize and WP Rocket both support scheduled cleanup. Configure them during off-peak hours.

Monitoring Database Size

Track your database size to catch bloat early. Run this query to see individual table sizes:

SELECT table_name AS 'Table',
       ROUND(data_length / 1024 / 1024, 2) AS 'Data (MB)',
       table_rows AS 'Rows'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

If wp_options exceeds 5 MB or wp_postmeta grows beyond 100 MB, it is time for a cleanup. These tables are queried frequently, so their size directly affects page load times.

For a comprehensive performance optimization approach that includes database optimization alongside caching, image optimization, and server configuration, read our WordPress speed optimization guide. For ongoing database health as part of your maintenance routine, see our WordPress maintenance guide.

For more details, refer to the official documentation: WordPress Database API.

Frequently Asked Questions

Is it safe to delete post revisions from the database?

Yes, deleting post revisions is safe and does not affect your published content. Revisions are historical snapshots of draft changes. Once your content is finalized, revisions serve no purpose for most sites. However, always back up your database before performing any cleanup.

Will database optimization improve my site speed noticeably?

The improvement depends on how bloated your database is. Sites with 50,000+ orphaned rows or a wp_options table exceeding 10 MB will see noticeable improvements in page load times and admin dashboard responsiveness. Smaller sites may see marginal gains. Database optimization works alongside caching and other performance measures for cumulative improvement.

How often should I optimize my WordPress database?

For active sites with regular content updates, monthly optimization is a good baseline. High-traffic sites with frequent comments, WooCommerce transactions, or form submissions may benefit from weekly transient and spam cleanup with monthly table optimization.

Can database cleanup plugins break my site?

Reputable plugins like WP-Optimize and WP Rocket are designed to be safe. They target known bloat categories (revisions, transients, spam) and do not touch essential data. The risk increases when manually deleting plugin-specific tables. If unsure whether a table is needed, leave it in place and research its purpose first.

What is the difference between OPTIMIZE TABLE and deleting rows?

Deleting rows removes data from the table. OPTIMIZE TABLE reorganizes the physical storage of the table after rows have been deleted, reclaiming disk space and defragmenting the data file. Think of it as deleting files (removing rows) versus defragmenting your hard drive (optimizing the table).

Should I use InnoDB or MyISAM for WordPress tables?

WordPress uses InnoDB by default since version 5.5, and it is the recommended storage engine. InnoDB supports row-level locking (better for concurrent users), transactions, and crash recovery. MyISAM is faster for read-heavy operations but lacks these reliability features. If you find MyISAM tables in your database, converting them to InnoDB is generally recommended.

How do I identify tables left by deactivated plugins?

Advanced Database Cleaner plugin can detect and categorize non-core tables. Alternatively, run the SQL query SHOW TABLES and compare the table names against WordPress core tables and your currently active plugins. Tables with prefixes matching deactivated plugins are candidates for removal.

Does WooCommerce add significant database bloat?

Yes. WooCommerce stores order data, product metadata, customer sessions, and transients that grow substantially on active stores. Use WooCommerce > Status > Tools to clear expired transients and sessions regularly.