Editor's Note: This article on WordPress database optimization was originally published on June 20, 2014. It was updated on September 29, 2018.
If you're managing your own WordPress site, you definitely want and need to keep it cleaned up and in optimal form. Part of doing this is getting rid of the excess overhead that accumulates in your WordPress database. Spam comments and other digital detritus can cause your database to balloon in almost no time. Because of this, WordPress database optimization is a helpful trick that can get rid of the digital junk and speed up your website.
Just as you sometimes need to recharge to get your brain back in tip-top shape, so you need to clean out the junk that accumulates in your database over time, just from use.” ~ Luvvie Ajayi
Luvvie over at Awesomely Techie has written a great tutorial called “How to Optimize your WordPress Database,” and it's definitely worth checking out. In her tutorial, he shows the following shift in size after the MySQL database was optimized. The move from 126 MB to 39 MB is a drastic reduction and it clearly shows WordPress database optimization is worth your time and attention.
Using a couple of tools and some judicious tweaking of the database with phpMyAdmin, Luvvie was able to get rid of tens of thousands of rows of data that were gumming up her database and was able to shrink her database by about 70 percent. That is impressive!
Manually optimize database tables in MySQL
Optimizing your MySQL database removes the gaps in data or "overhead" that can occur in the everyday website operation. This can have the side effect of restoring optimal performance to your website if its database contributes to its slowing.
You can manually optimize your database with the following five steps:
- Before you begin, you need to back up your database. For more information, see Backing up and Restoring MySQL or MSSQL Databases.
- Access phpMyAdmin for the database you want to use (Web & Classic / cPanel / Plesk).
- Double-click the Database Name.
- At the bottom of the list of tables, click Check all tables having overhead
- From the drop down menu, select Optimize table.
Now that wasn't that hard, was it?
Plugins for easy WordPress database optimization
Manual optimization isn't for everyone, so let's explore some alternate options for cleaning up your database. WordPress does offer a number of quality plugins that can help you optimize your database quickly and easily.
WP-Sweep by Lester 'GaMerZ' Chan
Lester Chan's optimization plugin is an oldie, but a goodie. It allows novice WordPress users to clean up their database by optimizing the following items:
- Auto drafts
- Deleted comments
- Unapproved comments
- Spammed comments
- Deleted comments
- Orphaned post meta
- Orphaned comment meta
- Orphaned user meta
- Orphaned term meta
- Orphan term relationships
- Unused terms
- Duplicated post meta
- Duplicated comment meta
- Duplicated user meta
- Duplicated term meta
- Transient options
Advanced Database Cleaner by Younes JFR
This database plugin has 30,000 active installs and offers a premium version for additional functions and features. The free version provides optimization assistance with:
- Old revisions of posts and pages
- Old drafts of posts and pages
- Old auto drafts
- Pending comments
- Spam comments
- Trash comments
- Orphan postmeta
- Orphan commentmeta
- Orphan relationships
- Orphan dashboard transient feeds
In addition, you can also:
- Choose what items to clean-up
- Schedule your database optimization to run automatically
- View and clean cron tasks (scheduled tasks)
- View and clean tables
- View and clean options
WP-Optimize by David Anderson, Ruhani Rabin, Team Updraft
WP-Optimize is a solid tool for automatically cleaning up your WordPress database. Its main features include removing all unnecessary data, pingbacks, trackbacks and expired transient options, compact/de-fragment MySQL tables, automatically perform weekly optimization tasks, and it also displays database statistics and potential savings in storage.
Optimize Database after Deleting Revisions by CAGE Web Design
This optimization plugin is a one-click WordPress database optimizer that lets users quickly delete revisions of posts, pages, and custom post types. It also allows users to delete trashed posts, pages, comments, unused tags, and spammed comments. It has over 100,000 active users, so it is very popular.
Time to get cleaning
As you can see, you have lots of options for cleaning up and optimizing the WordPress database. From manual optimization to quick one-click plugins, cleaning up your MySQL database is easy.