Over time, WordPress databases accumulate unnecessary data such as old revisions, trashed items, and unused tables, which can slow down your website and affect its performance. Regularly cleaning and optimising your database can improve site speed, reduce server load, and enhance overall efficiency.
In this guide, we’ll walk you through the steps to clean up and optimise your WordPress database safely and effectively, ensuring better performance for your website.
Why Clean Up and Optimise a WordPress Database?
Here are the key reasons why regular database maintenance is essential:
- Improve Site Speed: A bloated database can slow down queries, causing your website to load slower, especially on large websites with lots of content.
- Reduce Server Load: By cleaning out unnecessary data, your server will have fewer resources to handle, resulting in faster processing times.
- Free Up Space: Unused data takes up space in your database, increasing the size of backups and making it harder to manage.
- Improve Performance: An optimised database reduces overhead, making it easier and faster for WordPress to access and retrieve information.
Learn: The Most Important Core Web Vitals Metrics
WordPress Database Optimisation and Clean Up Strategies
Now, let’s look at how you can clean and optimise your WordPress database.
Back Up Your WordPress Database
Before making any changes to your database, backing up your database is essential. A backup ensures that you can restore your website if something goes wrong during the cleanup process.
Here’s how to back up your WordPress database:
- Use a Backup Plugin: Plugins like UpdraftPlus, BackupBuddy, or BlogVault can automate backups and allow you to restore your site with a single click.
- Manual Backup via phpMyAdmin: If you prefer manual backups, log into your hosting control panel (like cPanel), go to phpMyAdmin, select your WordPress database, and click Export. Choose the Quick export method and download the SQL file.
Once your database is backed up, you’re ready to start cleaning and optimising.
Delete Post Revisions
WordPress automatically saves revisions every time you update a post or page. While this feature is helpful for restoring previous versions, over time, revisions can add unnecessary data to your database, especially if you update content frequently.
Here’s how to clean up post revisions:
- Using a Plugin: Plugins like WP-Optimize or Optimize Database after Deleting Revisions allow you to remove post revisions in bulk. Simply install the plugin, navigate to the revision cleanup option, and delete old revisions.
- Manually Delete Revisions via phpMyAdmin: If you’re comfortable working with SQL, you can manually delete revisions by running the following SQL query in phpMyAdmin:
DELETE FROM wp_posts WHERE post_type = 'revision';
To prevent excessive revisions from accumulating in the future, you can limit the number of revisions WordPress stores:
- Add the following line to your wp-config.php file:
define('WP_POST_REVISIONS', 3);
This will limit WordPress to keeping only the last 3 revisions of any post.
Remove Unused Media Files
When you upload media to WordPress, it generates multiple copies of each image in different sizes (thumbnail, medium, large, etc.), which can bloat your database. Additionally, if you’ve deleted posts or pages that used specific media files, those files may still be stored in the database.
Here’s how to clean up unused media files:
- Use a Media Cleanup Plugin: Plugins like Media Cleaner or WP-Optimize scan your media library for unused files and allow you to delete them. Be sure to review the list of files carefully before deletion to avoid removing media that is still in use.
- Manual Cleanup: If you prefer manual methods, you can check for unused files via your hosting file manager or FTP, comparing media files with what’s actually in use on your website.
Read: How to Make a WordPress Disaster Recovery Plan
Clear Out Spam and Trashed Comments
WordPress stores all comments, including spam and comments marked for deletion, in the database. These can pile up over time, especially if your site gets a lot of user interactions. Cleaning out spam and trashed comments regularly can free up significant space.
Here’s how to delete spam and trashed comments:
- From the WordPress Dashboard: Navigate to Comments > Spam and click the Empty Spam button. Do the same for Trash.
- Using a Plugin: You can automate this task using plugins like Advanced Database Cleaner.
- SQL Query via phpMyAdmin: Alternatively, you can run a SQL query in phpMyAdmin to clear out spam comments:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
To automatically delete spam comments after a certain period, you can add this function to your functions.php file:
function delete_spam_comments() {
global $wpdb;
$wpdb->query("DELETE FROM wp_comments WHERE comment_approved = 'spam'");
}
add_action('wp_scheduled_delete', 'delete_spam_comments');
Delete Unused Plugins and Themes
Every plugin and theme you install adds data to your WordPress database, even if you deactivate them. Deleting unused plugins and themes can help reduce clutter and optimise your database.
Here’s how to clean up unused plugins and themes:
- Go to the WordPress Dashboard: Navigate to Plugins > Installed Plugins and delete any inactive or unnecessary plugins.
- For Themes: Go to Appearance > Themes and delete any themes you’re not using, except for a backup theme like Twenty Twenty-One (for troubleshooting purposes).
Check out: What Are Website Vulnerabilities and How Can Hackers Exploit Them
Optimise Database Tables
WordPress stores content in various database tables, which can become fragmented over time. Optimising database tables defragments them, making data retrieval more efficient and improving site performance.
Here’s how to optimise database tables:
- Using a Plugin: The WP-Optimize plugin makes database optimisation easy with a one-click solution. It allows you to optimise all tables without needing to access phpMyAdmin.
- Manual Optimisation via phpMyAdmin: You can manually optimise tables by accessing your database via phpMyAdmin, selecting your database, and clicking on the Check All option at the bottom. Then, choose Optimise Table from the dropdown menu.
Remove Transients
WordPress uses transients to temporarily store cached data, like API calls or plugin-specific information. However, transients are often left in the database long after they’ve expired, adding unnecessary load.
Here’s how to clean up transients:
- Using a Plugin: The Transients Manager plugin allows you to view and delete expired transients. Additionally, plugins like WP-Optimise or Advanced Database Cleaner can also help clean up transients.
- Manual Cleanup: If you’re comfortable with SQL, you can run the following query in phpMyAdmin to delete expired transients:
DELETE FROM wp_options WHERE option_name LIKE '_transient_%';
Clean Up the wp_options Table
The wp_options table stores many settings, including plugin and theme options. Over time, it can accumulate unnecessary or outdated options, particularly if plugins or themes don’t properly clean up after being uninstalled.
Here’s how to clean up the wp_options table:
- Manual Review: Access the wp_options table via phpMyAdmin and review any entries marked as
autoload = yes
. These are loaded on every page load, so it’s important to ensure they’re necessary. - Use a Plugin: Plugins like Advanced Database Cleaner can help identify and clean up orphaned entries in the wp_options table.
Automate Regular Database Optimisation
While manual cleanups are effective, it’s best to automate the process for regular database optimisation. You can schedule optimisations to run automatically so your database remains efficient without manual intervention.
Here’s how to set up automated database optimisation:
- WP-Optimize: This plugin offers a scheduling feature that lets you set regular intervals for database cleanups (weekly, monthly, etc.).
- Advanced Database Cleaner: This plugin also provides automatic scheduling for regular cleanups and optimisation.
By automating the process, you ensure your database remains clean and optimised over time without needing to perform manual tasks regularly.
Know more: How To Easily Backup Your WordPress Site
Limit Auto-Drafts and Trash Retention
WordPress automatically saves auto-drafts of posts and pages as you write, which can accumulate in your database if not managed. Similarly, trashed posts, pages, and comments are stored for 30 days by default before being permanently deleted.
Here’s how to reduce auto-drafts and trash retention:
- Limit Auto-Drafts: Add the following line to your wp-config.php file to change how frequently auto-drafts are saved (in seconds):
define('AUTOSAVE_INTERVAL', 300); // Save every 5 minutes instead of 1 minute
- Change Trash Retention Period: To reduce the retention time for trashed items, add this line to wp-config.php:
define('EMPTY_TRASH_DAYS', 7); // Empty trash after 7 days instead of 30
These settings will reduce the number of unnecessary drafts and trashed items stored in your database.
Conclusion
Cleaning up and optimising your WordPress database is essential for maintaining a fast, efficient, and scalable website. By removing old revisions, unused media, spam comments, and transient data, and by optimising database tables, you can significantly improve performance and reduce server load.
Incorporating regular database maintenance, either manually or through automation, will ensure that your WordPress website continues to run smoothly, providing a better user experience and supporting future growth.