How to Optimize MySQL Tables in WordPress
I have used WP-Robot in the past to populate some of my blogs with content. Its a great way to grab content ideas from the major article directories and then rewrite/modify the article – add your own information and post. A much better way than starting from scratch writing an article for a site.
Unfortunately, on my site GastricBypassIssues.com, the draft posts in WordPress caused the size of the database to swell to more than 300MB. This caused a issue with the entire site since the site was now over the space limit for the account. Even after deleting the drafts in WordPress, the size of the posts table was still over 280MB. No matter what I did, I could not get the size of the posts table to its actual size.
Beyond the number of posts in WordPress, the MySQL database includes all the draft revisions, comments, spam comments, unapproved comments and anything else entered into WordPress. So, its a good idea to optimize and clean up this database every so often to reduce the amount of space being used by your site.
Luckily, there is a great plugin to optimize a MySQL database being used by WordPress. Its called WP-Optimize and it does the following things:
- Remove all Post revisions
- Remove all auto draft posts
- Clean marked Spam comments
- Clean Unapproved comments
- Optimize database tables
After running WP-Optimize, the size of the posts table was reduced to less than 2MB. A much more accurate size for the number of posts on the site.
Warning: I did test another “optimizer” plugin before settling on WP-Optimize. The other plugin failed during the optimization and left my site with no posts found, which I then had to repair using some MySQL commands in PHPAdmin before installing WP-Optimize and running it. For this reason, even though the other plugin had a better star rating, I recommend WP-Optimize for its easy of use and rock solid performance.
UPDATE: I discovered an even better plugin called WP Clean Up, that helped reduce the size of one of my WordPress sites from 90MB to 30MB just by running it and cleaning up the MySQL database.
Filed under: Tutorials
Like this post? Subscribe to my RSS feed and get loads more!