Optimizing and Cleaning Up Your WordPress Database: A DIY Guide

As your WordPress project grows, you may begin to notice performance issues, often caused by a bloated database. While there are many plugins available to help clean up your database, sometimes it's good to know how to do it yourself. This guide will walk you through manually cleaning and optimizing your WordPress database.

Backup first - It's Critical!

Before making any changes to your database, always create a backup. The steps below involve running destructive SQL queries that will permanently delete data, and you don’t want to lose anything important. A backup will ensure that you can recover in case anything goes wrong.

Plugin Alternatives

If you’re not comfortable running SQL queries manually, don’t worry—there are some excellent plugins that can automate the process for you. Here are a few highly recommended options:

• Advanced Database Cleaner
• WP-Sweep
• WP-Optimize

These plugins handle a variety of database cleanup tasks efficiently. Even if you go the plugin route, you should still create a backup before running any database cleanup operations, just to be safe.

What Are We Cleaning?

The goal is to remove unnecessary data that accumulates in your database over time—data you no longer need but which can slow down your site. Here's what we'll be targeting:

• Transients: Temporary data stored in the database.
• Revisions: Old post versions.
• Auto-Drafts: Automatically saved drafts that were never published.
• Orphaned Post Meta: Metadata for posts that no longer exist.
• Spam and Trashed Comments: Unwanted comments cluttering your database.
• Orphaned Relationships: Unused term relationships (tags, categories).
• Expired Sessions: Old user session data.
• Old Plugin Options: Unused options left behind by deactivated or deleted plugins.
• Unattached Media: Media files without a parent post (though this won't delete the actual files).

By removing these unnecessary items, you can significantly improve your WordPress database performance.

Remember to backup your database before proceeding. Additionally, we’ll wrap all SQL statements in a transaction, allowing you to roll back changes if something doesn’t go as planned.

Enhancing Database Performance

Aside from cleaning your database, you can also improve performance by optimizing frequently used tables. This reduces fragmentation and keeps your database running smoothly.

Ready to Clean?

If you’re confident and ready, copy and paste the following SQL queries into your WordPress server’s SQL console. Feel free to skip any queries by commenting them out or removing them.

If your WordPress database uses a custom table prefix, replace the default wp_ prefix in the queries with your own.

Optimization Query

This query optimizes some of the most commonly used WordPress tables:

-- Optimize commonly used WordPress tables to reduce fragmentation
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_usermeta, wp_comments;

Cleanup Query

Here’s the cleanup portion, wrapped in a transaction so that you can roll it back if needed:

-- Start the transaction
START TRANSACTION;

-- Remove transients (temporary data)
DELETE FROM wp_options WHERE option_name LIKE '_transient_%' OR option_name LIKE '_site_transient_%';

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

-- Remove auto-drafts
DELETE FROM wp_posts WHERE post_status = 'auto-draft';

-- Remove orphaned post meta (meta data for non-existent posts)
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

-- Remove orphaned comment meta
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

-- Remove spam and trashed comments
DELETE FROM wp_comments WHERE comment_approved IN('spam', 'trash');

-- Remove orphaned term relationships (tags, categories linked to non-existent posts)
DELETE tr FROM wp_term_relationships tr LEFT JOIN wp_posts wp ON wp.ID = tr.object_id WHERE wp.ID IS NULL;

-- Remove expired user sessions
DELETE FROM wp_usermeta WHERE meta_key = '_wp_session_expires' AND meta_value < UNIX_TIMESTAMP();

-- Remove old, non-autoloading plugin options
DELETE FROM wp_options WHERE autoload = 'no';

-- Identify unattached media files (this won't delete the files, just shows them)
SELECT * FROM wp_posts WHERE post_type = 'attachment' AND post_parent = 0;

-- Commit the transaction (to apply changes)
COMMIT;

Rollback If Something Goes Wrong

If you encounter any issues or if something doesn’t look right, you can cancel the transaction and revert the changes:

-- Rollback the transaction if you don't want to commit the changes
ROLLBACK;

Conclusion

Regularly cleaning and optimizing your WordPress database is essential for maintaining good performance, especially as your site grows. Whether you prefer to do it manually or use a plugin, the important thing is to ensure that your database doesn’t become a bottleneck.

By following this guide, you can clean up and optimize your database safely and efficiently, keeping your site running smoothly. And remember: always, always back up before making changes!