How to Clean Up Your WordPress Database
7-layers of Security for Your WordPress Site
Your website needs the most comprehensive security to protect it from the constant attacks it faces everyday.
Do you want a fast WordPress website? If so, you need to clean up WordPress database by removing unnecessary data.
The WordPress database stores website content in posts, pages, and other post types. It also stores comments, links, portfolio items, form entries, plugin settings, theme settings, media settings, and more.
Unfortunately, if left unchecked, WordPress databases quickly become bloated with data that is no longer required. This increases the size of the database and the time it takes to retrieve information, which inevitably makes your website slower.
The goal of WordPress database optimization is to remove unnecessary data so that your website will be more efficient and load pages faster.
Let’s take a closer look at why WordPress databases increase in size and then review how you can clean up database in WordPress 🙂
Why Do WordPress Databases Increase in Size?
Whenever you update your website and add new content, the WordPress database will grow in size. For example:
- Add a New Page – A new row will be added to the wp_posts table to store the page content
- Receive a New Comment – A new row will be added to the wp_comments table to store the comment
- Upload an Image – A new row will be added to the wp_postmeta table defining the image URL
Whilst the core files of WordPress plugins and themes are stored in the WP-Content directory, all plugin and theme settings and content are stored in the WordPress database in new database tables and additional database rows.
So every time you activate new plugins and themes on a WordPress website, the database grows larger. Unfortunately, the vast majority of plugins and themes do not remove the tables and rows they added to the database when they are deactivated and deleted.
The Importance of Backing Up Your WordPress Site
WordPress database cleanup and optimization should not break your website, however, I strongly recommend performing a website backup before you make any changes. This allows you to revert to your backup database if you accidentally remove data that you intended to retain.
On my personal blog, I use BlogVault to automatically back up my website daily and use the BlogVault backup tool to perform one-off backups when required.
Good WordPress Database Cleanup Practices
By following good WordPress database cleanup practices, your website database should never grow too big.
Recommendation #1: Prevent Comment Spam
Spam comments and other unwanted comments can greatly increase the size of the WordPress database. You can reduce the effect of comments by disabling trackbacks and pingbacks and using anti-spam WordPress plugins such as Akismet, Antispam Bee and CleanTalk.
If spam becomes a major problem on your website, you may want to follow my lead and completely deactivate comments altogether.
Recommendation #2: Limit Post Revisions
The WordPress revision system saves a revision each time you save a draft or publish a post, page or custom post type. It also saves a copy of your content automatically every 60 seconds. These are known as auto-saves or auto-drafts.
I’ve always found the revision system useful as it allows me to compare older drafts of articles, but it can also be a great waste of resources. Whilst only one auto-save is ever stored for a post or page, WordPress does not have any limit to the number of revisions that are saved. This can result in hundreds of variations of an article being stored in the WordPress table.
A practical way to tackle this is to limit the number of post revisions that are saved. You can restrict WordPress to three post revisions by adding the following line to the wp-config.php file.
define( 'WP_POST_REVISIONS', 3 );
The WordPress revision system can be disabled with the following line.
define( 'WP_POST_REVISIONS', 0 );
In the quest for efficiency, some website owners do use the above code to disable post revisions altogether. This is something I personally advise against as it leaves you with no backups of draft articles. A better option is to limit the number of post revisions that are saved and then delete those revisions once the content has been published.
Recommendation #3: Remove Unnecessary WordPress Plugins & Themes
Don’t fall into the habit of leaving unactivated WordPress plugins and themes on your website. If they are not being used, their files should be deleted and their data should be removed from the WordPress database.
Some plugins and themes do have an uninstall option in the settings area which removes all data, but unfortunately, most WordPress products leave all their data behind. Whilst leftover data can be removed from the WordPress database manually, database optimization solutions such as WP-Optimize and Plugins Garbage Collector are safer and they simplify the process by highlighting all unused tables for you (more on this later).
Recommendation #4: Remove Unused Media
Media that is not being used on your website will still be stored in the WordPress database. This has become a problem that is difficult to avoid as WordPress plugins and themes frequently import media when they are activated, but they fail to remove them later. This can result in many unattached icons, screenshots, unused thumbnails and other unwanted images.
The WordPress Media Library shows which media is not attached to content, but it can be time-consuming to validate media and check each item to see if they are required. I recommend using the WordPress plugin Media Cleaner instead as it will search for media that is not being used and let you delete them all with one click. In just a few minutes, the plugin found over 1,200 unused images from my WordPress media library.
Recommendation #5: Optimise Each Database Table
If you’re comfortable using a database administration tool such as phpMyAdmin to edit the WordPress database, you can utilise the Optimize Table feature to reduce storage space and improve I/O efficiency. Experienced users can also do this using the MySQL statement OPTIMIZE TABLE.
Don’t worry if this is all too technical for you as popular WordPress database cleanup plugins let you perform this WordPress database optimization at the click of a button.
Clean Up WordPress Database Using Plugins
For most website owners, WordPress database cleanup plugins are the most practical way to clean the WordPress database.
Its WordPress database optimization plugin allows you to optimize database tables and clean up post revisions, auto-saved drafts, trashed posts, and metadata. Spam comments, pingbacks, trackbacks and expired transient options, can also be removed. Optimizations can be performed manually or scheduled to be processed daily, weekly fortnightly or monthly.
One of WP-Optimize’s most useful tools is its database table scanner. It highlights tables that are not being used and shows the number of records and data size for each one. A link to the associated WordPress plugin is provided for each table and tables that are not required can be removed at the click of a button.
2. WP Rocket
From the WordPress database cleanup page, you can delete post revisions, auto-drafts, trashed posts, spam comments, trashed comments, and transients. WordPress database tables can also be optimized.
WP Rocket’s scheduling tool helps you automate WordPress database optimization. Automatic cleanups can be performed daily, weekly, or monthly.
The plugin that I frequently use to search the WordPress database for leftover tables is Plugins Garbage Collector. It works in the same way as WP-Optimize’s table scanning tool, highlighting tables that are not being used and showing the number of records and data size. Plugins Garbage Collector can also check your WordPress table structure and scan for hidden database tables.
Whilst currently installed plugins are correctly highlighted as active by Plugins Garbage Collector, the plugin does struggle to find associated WordPress plugins for unused database tables.
4. WP Sweep
WP Sweep is a database optimization solution that can clean up post revisions, auto drafts, spam comments, orphaned data, duplicated meta information, and more. There is an option to optimize database tables too.
On my personal blog, I limit post revisions to three, yet WP Sweep showed that I still had a whopping 2,179 post revisions. This represented 11.15% of the total size of my database.
In comparison to other WordPress database optimization plugins, WP Sweep highlights more orphaned data and duplicated meta information, but as there is no optimization scheduling functionality, you need to perform all database cleanups manually. This is not necessarily a bad thing as it forces you to review each database cleanup.
Perfmatters is a WordPress security and performance toolbox that provides dozens of different tools to reduce page loading times. On its main options page, Perfmatters allows you to limit post revisions or disable them altogether. The auto-save interval can also be changed from the default value of minute to two, three, four, or five minutes.
On the main database optimization page, Perfmatters allows you to clean up post revisions, auto-drafts, trashed posts, spam comments, trashed comments, and transients. The database can also be optimized and you can schedule optimizations to be processed daily, weekly, or monthly.
WordPress database optimization is one of the most important maintenance tasks for a WordPress website. By removing unnecessary data from your website database, you will reduce the size of the database and reduce page loading times.
I believe WP-Optimize is the best overall WordPress database cleanup and optimization solution available to WordPress users today as it can remove unused data and scan for unused tables from deleted plugins too. Those of you who are already using WordPress performance solutions such as WP Rocket or Perfmatters may want to consider the WordPress database optimization plugins that are already available to them.
You may also like
When it comes to managing a WordPress website, WordPress logs are an indispensable diagnostic tool. They provide a comprehensive record of website activities in real-time and help track a wide…
As new site owners, navigating your way through the world of website security can be daunting. A pervasive notion across numerous articles online is that changing your database prefix is…
When it comes to troubleshooting issues on your WordPress site, WordPress error logs are a godsend. Logs are snapshots about issues on your site, showing verbose error messages so you…
How can we help you?
If you’re worried that your website has been hacked, MalCare can help you quickly fix the issue and secure your site to prevent future hacks.