Why you need to optimize your WordPress MySQL database
The database is the most important component in the whole WordPress content management system. Everything you enter in the WordPress Dashboard including all the content of your posts and pages, categories, tags, settings, etc., is written to the database. WordPress and the various themes and plugins read this database to retrieve your content and settings. Very odd problems can occur on your site if the database is not tuned up regularly. The first thing I do when troubleshooting a problematic site is take a look at the error logs on the server as well a backup and optimize the database.
Using the Optimize Database After Deleting Revisions WordPress plugin
I’ve tried several different database optimization plugins for WordPress, and the best one I’ve found so far with the most streamlined yet full-featured interface is Optimize Database After Deleting Revisions. It does the following:
- Deletes redundant revisions of posts and pages (you optionally can keep a specified number of the most recent revisions)
- Deletes trashed posts, pages and comments (optional)
- Deletes spammed comments (optional)
- Deletes unused tags (optional)
- Deletes ‘orphan postmeta items’
- Deletes ‘expired transients’
- Optimizes the database tables (optionally you can exclude certain tables from optimization)
- Creates a log file of the optimizations (optional)
- Optimization can be scheduled to automatically run once hourly, twice daily, once daily or once weekly at a specific time (optional)
- ‘Optimize DB (1 click)’ link in the admin bar (optional)
Every time you click the Update button on a post or page, a copy of that post or page is saved in the WordPress database. You don’t need all of these revisions, and they cause your database to be unnecessarily bloated. Same with spam comments. Depending on the popularity of your blog, spam comments by the tens of thousands can accumulate in the database without your knowing it.
After installing the plugin, go to Settings > Optimize DB Options and set the number of revisions you want to save. I usually save 2 and check all the options (delete trashed items, delete spam comments, delete unused tags, etc.). The only thing I don’t do is save a log. I also set the plugin to automatically run once a week.
After saving the options, go to Tools > Optimize Database and run the optimizer. Scroll down the page and take note of all the database tables and their sizes. Take note of any particularly large tables (sizes in the MB rather than KB) that are not the wp_posts or the wp_options table, such as any tables related to website forms, logs, old plugins, etc.
Tip: You may want to manually run the Optimization too, so you can check the size of the database tables and make sure none of them are getting too large. This will clue you into things you might want to attend to, such as deleting logs (see below).
Deleting logs from the WordPress database
When you optimize your database, you may notice that you have large log tables. Frequently used plugins that create logs in the database include:
- Broken Link Checker
- Optimization plugins
- Redirection plugins
- Security plugins
- Poorly coded statistics plugins, including some social network plugins
Most of these plugins provide an option to delete log files. Generally, you don’t need these plugins to be logging everything all the time. For a specified period of time, you may want to log and monitor 404 errors on your site using a redirection plugin. However, you can also monitor 404 errors using Google Webmaster Tools, so you don’t need to bloat your database with these logs if you are already paying attention to Google Webmaster Tools.
My advice? Log consciously for a specific amount of time, then turn the logging feature off in whatever plugin you are using. (See instructions below for deleting logs created by the Wordfence plugin.) Then, delete all the old logs. If the plugin does not provide the option for disabling logging, then be sure to login to your WordPress Dashboard regularly and check then delete these logs periodically.
Broken Link Checker is a popular plugin that can be resource intensive. If you are concerned about keeping track of broken links, you can use this free broken link checker website: http://www.brokenlinkcheck.com/ (limited to 3000 pages)>
WP Engine recommends the following applications that you can install on your computer to check for broken links:
- Xenu Link Sleuth — Windows only.
- Integrity — Macintosh only.
Deleting form entries (such as Gravity Forms) from the WordPress database
If you use Gravity Forms or other form plugins, the entries might be stored in the database and accumulate quickly. You can usually export these entries to a CSV file and then delete them, so they aren’t stored in the database. This is a good idea for privacy protection anyway.
This plugin can be used to delete all Gravity Forms entries if you have many pages worth of entries to remove: https://wordpress.org/plugins/gravity-forms-remove-entries/
Deleting/Dropping Old WordPress Plugin Tables
Cleaning up and optimizing the database is a very good idea before you attempt to migrate to a new web host, particularly on older sites that may have a lot of dust bunnies leftover from old plugins. Get rid of those tables and optimize the rest!
If your database contains tables belonging to a plugin that you no longer use, you will need to log into your web hosting control panel and access the database via phpMyAdmin and manually delete these tables. (Here are instructions on how to delete, aka drop, tables from your database. Be sure to backup your database before doing this! Do not drop any tables related to any active plugins.)
Manually Optimizing Your WordPress Database
If for some reason you have lost access to your WordPress Dashboard due to a potential problem with the database, you can manually check, repair and optimize your database using phpMyAdmin and this will often bring back up the website.
WordFence creates a lot of tables/entries and my database size is increasing day by day. I have used a lot optimization plugins but to no help. can you suggest something? Thanks.
Hi Bahawalpur,
This is why I have stopped using Wordfence. It bloats the database and causes lots of PHP error logs to build up.
You can get rid of the Wordfence tables by going to the Wordfence Options and checking the box toward the bottom of the settings page that says to Delete Wordfence tables and data on deactivation?. Click the Save Changes button, then de-activate the plugin. If you want to keep using it, you can re-activate and reset your settings. I would recommend you turn off the Enable Live Traffic View as that is what bloats the database so tremendously.
Let me know how that goes!
Angela
How to check if indexes are properly created or not in db, i doubt one plugin installed on my site is creating lot of entries & i dont think the table is optimized
That’s a good question. Some plugins do create a lot of entries if they contain log files. What plugins are you using?
Great timing on this post! Now that we are wrapping up our year this is a great housekeeping item to do for the heavier sites that we manage.
You always come through with a great plug-in idea or remind us of an amazing free tool like brokenlinkcheker.com.
Keep up the great work!
Matt
I’m so glad it will be helpful, Matt. The Optimize plugin is great for small sites, too. 🙂