Optimizing your WordPress database is important when it comes to keeping your site healthy and running fast. Over time things like this can easily start to add up to hundreds of thousands of rows. We’ve seen this more times than we can count! Just like you would oil and maintain your car, you should also do it with your WordPress site’s database.
Another reason is that an evergrowing database could cost you more on disk space with your hosting provider. So optimizing can also be a way to save money. Because of this, we have many ways in our Perfmatter’s plugin to clean up and delete leftover data, such as revisions, auto-drafts, spam comments, transients, and more.
A huge problem today is that some of the database optimization plugins out there are bloated and cause performance issues on the back-end. Some are even on hosting providers’ banned or disallowed lists. We’ve developed ours to be as lightweight as possible, while still giving you the options you need.
Click on any of the links below to jump to the more in-depth documentation on how to perform each individual optimization. Our biggest goal was to keep this feature simple and fast.
- Delete WordPress post revisions
- Delete WordPress auto-drafts
- Delete WordPress spam comments
- Delete WordPress trashed posts
- Delete WordPress trashed comments
- Delete WordPress transients (expired or all)
- Optimize WordPress database tables
- Schedule optimization
- CRON job
- Filter for HTTP Basic Auth (.htpasswd)
Schedule optimization
Don’t have time to go through each optimization on a regular basis? No worries, we have a built-in scheduler you can use.
Important: These functions make permanent changes that cannot be reverted! Back up your database before proceeding.
Step 1
Click into the Perfmatters plugin settings.
Step 2
Click on the “Tools” submenu.
Step 3
Click on the “Database” submenu.
Step 4
Select the database optimizations you want to run on a schedule. You can click on the tooltip on each optimization to learn more about them.
Step 5
Scroll down and under “Scheduled Optimization,” select the frequency at which you want it to run: Daily, Weekly, or Monthly. Note: This default is set to disabled.
Setting a new schedule will run the database optimization process immediately after saving your changes. This is performed with a background process and cron job in WordPress.
Step 6
Click “Save Changes.” You can go back in there at any time if you need to change or disable the schedule.
CRON job
The database optimization feature uses a background scheduler attached to a cron job schedule at the end of it to confirm the cleanup has finished. You can use a real cron (or server-level cron); it just means that the UI message in the Perfmatters plugin settings might remain longer after running to confirm cleanup has finished. The timing would depend on what you have set for the database optimization schedule.
The cron will run based on the schedule you set, say daily, weekly, etc. starting from the time you set it up. If you run a manual optimization, the schedule already set will remain the same.
Filter for HTTP Basic Auth (.htpasswd)
If you have HTTP Basic Auth (.htpasswd) protection on your WordPress site, you will need to add the filter below for the database optimization process to function properly.
We recommend using the free WPCode plugin. Check out this article on how to add PHP to your WordPress site.
Make sure to update the USERNAME and PASSWORD strings accordingly.
function wpbp_http_request_args( $r, $url ) {
$r['headers']['Authorization'] = 'Basic ' . base64_encode( USERNAME . ':' . PASSWORD );
return $r;
}
add_filter('http_request_args', 'wpbp_http_request_args', 10, 2);Troubleshooting
If the “Database optimization is running in the background” message doesn’t disappear after a long period of time, make sure you aren’t blocking requests such as /wp-admin/admin-ajax.php. It could be it is getting blocked/challenged due to a service like Cloudflare WAF.