As your website grows and you create more content, your WordPress database can become cluttered with unnecessary data, which can slow down your site and create security risks.
In this guide, we will discuss the best WordPress database cleanup methods and plugins to boost site speed and performance.
Your WordPress database stores all your website data, including posts, pages, comments, media files, and user information. As your website grows, your database can become bloated with unnecessary data, which can:
Maintaining a clean WordPress database is an effective step not only toward better website speed and performance but also:
Before performing any database cleanup, it's essential to perform a backup. This ensures you have a copy of your website's data in case anything goes wrong during the cleanup process.
To create a backup, you can use a plugin or access cPanel and use the built-in backup tool. It's important to save the backup in a secure location (like a Cloud space) so you can restore your website easily.
Generally, the manual approach requires access to your WordPress dashboard and phpMyAdmin – a web-based application for managing MySQL databases.
To access the phpMyAdmin interface, log in from your web hosting control panel or through a plugin like WP phpMyAdmin. Please note all SQL commands shared below use the standard “wp_” prefix. Change it to match the ones used by your database.
Note: Manual WordPress database cleanup requires some technical knowledge. If you don’t feel confident running the command line, we recommend you go with any of the tried-and-tested plugins. Jump to the list.
In general, you can optimize all database tables, but some may require more attention than others. Here are some tips on how to identify the tables that need optimization:
To optimize tables, select them from the list on the left in phpMyAdmin, and check the box next to them.
From the "With selected" drop-down menu, select "Optimize table." Click on the "Go" button to start the optimization process.
Unused data can accumulate over time, leading to a cluttered database. This includes unused themes and plugins, media files, posts and pages, and tags.
Unused Themes and Plugins
To delete inactive WordPress themes and plugins, run the following SQL commands in phpMyAdmin:
For unused themes:
DELETE FROM wp_options WHERE option_name LIKE 'template_%' OR option_name LIKE 'stylesheet_%';
For unused plugins:
DELETE FROM wp_options WHERE option_name = 'active_plugins';
Alternatively, log in to your WordPress dashboard and select the "Appearance" or “Plugins” options for inactive themes and plugins, respectively.
From there, you can select and delete them (in the example below, we’re deleting an inactive WordPress theme).
To delete inactive WordPress plugins completely, we suggest you follow our latest step-by-step guide.
Unused Media Files
To delete unused media files:
To remove unused media files in one hit, use the following command in the SQL tab:
DELETE FROM wp_posts WHERE post_type = 'attachment' AND post_parent = 0;
Unused Posts and Pages
To delete unused posts and pages via phpMyAdmin, you can use the following SQL commands:
DELETE FROM wp_posts WHERE post_type = 'post' AND post_status = 'draft';
DELETE FROM wp_posts WHERE post_type = 'page' AND post_status = 'draft';
Alternatively, you can delete unused posts and pages inside the WordPress dashboard in a few simple steps:
To remove unused tags in phpMyAdmin, you can use a combination of SQL queries.
Run the following query to identify all tags that are not associated with any posts or content:
SELECT * FROM wp_terms AS t
LEFT JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
WHERE tt.taxonomy = 'post_tag'
AND tt.count = 0;
This query will list all the tags that are not associated with any posts or content. Make sure that the list contains only the tags you want to delete.
To delete these tags run the following query:
DELETE FROM wp_terms WHERE term_id IN (
SELECT term_id FROM wp_term_taxonomy WHERE count = 0
This query will delete all the tags that have a count of zero, i.e., those that are not associated with any posts or content.
Removing unused tags is also done through your WordPress dashboard.
If the tag you want to delete is still associated with any posts, you will need to remove it from those posts first. To do this:
Once the tag is removed from all the associated posts, go back to the "Tags" section and repeat the steps from above.
In phpMyAdmin, run the following query to identify all comments that have been marked as spam:
SELECT * FROM wp_comments WHERE comment_approved = 'spam';
Once you have confirmed the list, you can delete these comments by running the following query:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
To remove spam comments via the WordPress dashboard, follow these steps:
Similarly to the spam comments, run the following query to identify all comments that have not been approved:
SELECT * FROM wp_comments WHERE comment_approved = 0;
Then, you can delete these comments by running the following query:
DELETE FROM wp_comments WHERE comment_approved = 0;
Alternatively, navigate to your WordPress dashboard. In the “Comments” section, select "Pending" or "Unapproved" from the "All Comments" drop-down menu.
To permanently delete the unapproved comments, go to the "Trash" section and perform the bulk action “Delete Permanently” for all the unapproved comments you want gone.
Click on the "Apply" button to finish the process.
To delete post revisions, enter the following command in the SQL query box and click Go to execute:
DELETE FROM wp_posts WHERE post_type = "revision";
For a more granular control, the safest alternative is to use a plugin from our recommendations list.
Identifying old shortcodes in WordPress can be challenging, especially if you have a large number of pages or posts on your site. However, there is a way to identify which shortcodes are old and no longer used on your site:
Note: We recommend using a plugin if you have a large number of posts. Going through each post to identify and delete a shortcode can prove more time-consuming than expected.
Once you identify which shortcodes are no longer needed, go to your WordPress dashboard.
Pingbacks and trackbacks are two methods that WordPress uses to notify you when another website links to your content. Both methods are designed to help you manage your incoming links and engage with other bloggers and website owners.
However, they can also be a source of spam and unwanted notifications, which is why it's important to manage them properly or disable them altogether.
In phpMyAdmin, run the following query to identify all comments that have the comment type of "pingback" or "trackback":
SELECT * FROM wp_comments WHERE comment_type = 'pingback' OR comment_type = 'trackback';
Delete these comments by running the following query:
DELETE FROM wp_comments WHERE comment_type = 'pingback' OR comment_type = 'trackback';
You could also use the built-in comment management system in your WordPress dashboard:
Transients are temporary pieces of data used to cache data and speed up your website's performance. However, if not taken care of on a regular basis, they can start harming your speed instead. Here's how to remove transients in a WordPress database cleanup:
1. via phpMyAdmin
Log into your phpMyAdmin. Then, select your WordPress database and click on the SQL tab. In the SQL window, enter the following command to delete all transients:
DELETE FROM wp_options WHERE option_name LIKE '_transient_%';
2. via WP-CLI
If you're comfortable using the command line, log in to your server and open a terminal window. Then, navigate to your WordPress directory and run the following command to delete all transients:
wp transient delete --all
Note: It's important to note that when you remove transients, they will be recreated the next time they are needed. Therefore, it's a good idea to regularly clean up your database to maintain a fast and efficient website.
Manually cleaning up your database can be a time-consuming and tedious task, especially if you're not familiar with SQL queries.
Fortunately, there are many plugins available that can automate the process for you. They can help you:
Using a plugin for database cleanup is generally safe as long as you choose a reputable plugin and follow best practices. Flip through our list of the best WordPress plugins for database optimization.
WP-Optimize is a popular plugin that can remove unnecessary data, such as old post revisions, spam comments, and unused tags. It can also optimize your database tables and remove expired transients. One of the standout features of WP-Optimize is the ability to schedule automatic cleanups, so you don't have to remember to do it manually. The plugin is easy to use and has a user-friendly interface.
WP Sweep can remove unused, orphaned, and duplicated data, as well as optimize your database tables. The plugin is lightweight and easy to use, with a simple interface. WP Sweep also includes a preview function, so you can see what data will be deleted before you confirm the cleanup.
Advanced Database Cleaner comes in both free and paid versions, with the paid version offering more features and functionality. The plugin is designed to help you clean up and optimize your database by removing unnecessary data, such as post revisions, spam comments, and unused tables.
One of the standout features of Advanced Database Cleaner is its ability to schedule automatic cleanups. The plugin also allows you to create custom queries to clean up specific parts of your database.
WP DBManager offers a range of features, including database backup and optimization options, as well as the ability to repair and restore your database.
One of the standout features of WP DBManager is its easy-to-use interface, which makes it simple to perform database maintenance tasks. The plugin also includes a range of optimization options to help you speed up your site, such as removing spam comments
and cleaning up post revisions.
WP Reset is a powerful WordPress plugin that helps users quickly and easily reset their website to its default settings. Not only can users take a snapshot of their website to quickly restore their website to a specific previous state, but also rely on efficient database cleanup and emergency recovery script.
When choosing a plugin for database cleanup, there are a few things to keep in mind.
First, look for a plugin that is regularly updated and has a large user base. This can help ensure that the plugin is compatible with the latest version of WordPress and is free from bugs and security vulnerabilities.
Second, look for a plugin that has a good reputation and positive reviews.
Finally, choose a plugin that meets your specific needs. Some plugins are designed to clean up specific types of data, while others offer more comprehensive cleaning and optimization options.
One of the best ways to ensure your WordPress database remains clean is to schedule regular cleanups. Depending on the size of your website and how frequently you update content, you may need to schedule cleanups more frequently.
For websites with a lot of traffic and frequent content updates, it's recommended to schedule cleanups on a weekly or bi-weekly basis. For smaller websites, a monthly or bi-monthly cleanup may suffice.
It's important to keep an eye on your database size to determine how frequently you need to schedule cleanups. You can use plugins like WP-Optimize, WP-Sweep, or Advanced Database Cleaner to monitor your database size and set up automatic cleanups. Alternatively, you can monitor your database size using cPanel.
Your WordPress database may be big because of accumulated data over time. This includes post revisions, spam comments, and unused data like media files, themes, and plugins. Additionally, some plugins may create their own tables in the database, which can also contribute to its size.
No, not everything is stored in the database. WordPress stores content like posts, pages, and comments in the database, but media files like images and videos are stored on your server's file system. Plugins and themes may also store data outside of the database.
If you accidentally delete important data from your database, it may result in errors or a broken website. This is why it's vital to always back up your database before performing any cleanup. You can then use the backup to restore any accidentally deleted data.
Deleting your WordPress database will result in a broken website. Without the database, WordPress won't be able to access any content, comments, or settings, and your website won't function properly.
А clean WordPress database is essential for optimal website speed, performance, and security.
There are several cleanup techniques that you can use to keep your database clean, including backing up your database, manually optimizing your database via phpMyAdmin by going over database tables and removing unused data like themes, plugins, media files, posts, and tags.
There are also several plugins available that can help you with WordPress database cleanup, including WP-Optimize, WP-Sweep, and Advanced Database Cleaner. When choosing a plugin, it's important to consider its features, ease of use, and reliability.
By scheduling regular cleanups and monitoring your database size, you can ensure that your website remains fast, secure, and optimized for optimal user experience. Prioritize a database cleanup today and reap the benefits tomorrow.
Lora has 7+ years of experience developing in-depth, specialized content for SaaS companies in the CEE region. She has sourced and collaborated with subject-matter experts on site speed optimization for WordPress, Core Web Vitals, and the future of web performance. "If you can't explain it to a 7-year-old, you've not researched it well enough" is Lora's guiding principle when publishing professional insight for complex technical topics.