Over time, your WordPress database accumulates clutter. There may be whole tables left behind by plugins that you are no longer using. There are almost sure to be expired transients, outdated options, and orphaned post or user meta. A bloated WordPress database can cause performance problems, and it may also make backing up and restoring more difficult.
Inspecting your database for problems is a little harder than looking for missing images or making sure your forms are working. If you’re not familiar with the WordPress database structure and tools like phpMyAdmin, it’s probably best to ask your developer to do the checking. It’s perfectly safe to install one of the database maintenance plugins I mention in the next section, though.
Before you do anything with your database, make a backup. You can do this with a WordPress plugin, from within phpMyAdmin, or from your hosting control panel.
Plugins for Cleaning Up Your WordPress Database
These plugins won’t remove unwanted database tables or stray options, but they will clear out excess revisions, orphaned meta, and expired transients, and they will also get rid of overhead. The two I use most often are WP-Optimize (now owned by the same people who make Updraft Plus) and WP-Sweep.
Of the two, I have a slight preference for WP-Sweep, though WP-Optimize lets you schedule optimizations and choose to retain some of the data. I like the detail it provides.
If your primary problem is that you have hundreds of revisions or heaps of expired transients, these plugins will make a huge difference. But they won’t tell you if you have orphan database tables.
Checking Your WordPress Database Tables
Most hosting companies give you access to your WordPress database through phpMyAdmin. Some use other tools, and a few won’t give you direct access to your database at all. (Here’s looking at you, Pagely and Flywheel.) I’m most familiar with phpMyAdmin, but I’m also getting to know Adminer a bit thanks to Local by Flywheel.
Log into your control panel, find your database management tool, and take a look. A clean WordPress install has only 12 tables, but if you’ve installed any plugins, you will probably have more. That’s okay–as long as all the extra tables are from plugins you’re still using.
The screenshot above shows a test install for Mergebot, a great new service from Delicious Brains. The Mergebot plugin adds three database tables (changesets, conflicts, and deployment_inserts). Since Mergebot is still installed, we want to keep those.
The database for Dorataya.com is another story. There are 39 database tables, 13 of which were created by plugins that are no longer installed on the site.
Identifying these orphan tables correctly is mostly a matter of familiarity with the plugins. If you’re not sure what plugin created the table, leave it alone until you can find out. The Advanced Database Cleaner plugin that I mention later in this post also identifies orphan tables…though not with 100% accuracy. (Did I mention you need to make a backup?)
Removing Orphan Database Tables
Once you’ve identified orphan database tables, removing them is easy.
- Back up your database before you make any changes.
- Check the boxes next to the tables you want to remove.
- Scroll down to the bottom of the database and choose “Drop.” (This is a button in Adminer and a drop-down selection in phpMyAdmin.)
- Confirm that you really want to drop the tables.
- Check to make sure the website still functions.
Removing WordPress Users
Many WordPress sites need only one or two user accounts: an administrator and an editor. Other sites have many legitimate users, such as customers of your store. Take a look at your user list and make sure that everyone on it belongs there. Is there still an account for the developer you fired two years ago? Delete it. An account for the hosting company you no longer use? Delete that, too. A bunch of spam signups with funny-looking email addresses that don’t match the user names? Definitely delete those. Getting rid of suspicious user accounts is mainly a security issue, but hundreds of spam accounts clutter your database.
The easiest way to delete unwanted WordPress users is to select them and then choose “Delete” from the “Bulk Actions” menu. If the users have created any posts, you will be asked whether you want to delete them or to attribute the posts to a different user. Spam accounts are unlikely to have any posts, so you won’t have to worry.
Now for the Scary Part: Cleaning Up Options
10up recently published a great article about keeping your wp_options table in check. If you have too many rows in this table, your performance takes a nosedive. Both plugins and themes add options, and these can accumulate in a positively frightening way. It can be even more frightening trying to clean them up without breaking anything–and it’s a huge slog, too.
For detecting orphan options and tables, and a lot of other database maintenance, the premium version of Advanced Database Cleaner is the way to go. For a detailed account of how to use Advanced Database Cleaner, see this review on WP Sutra.
I used it with success on the East Bay WordPress Meetup’s database. That one had 66 tables in it, about half of which I was able to remove, and a ton of old options. (I first built that site in 2009, and had experimented with a lot of different plugins.)
After you purchase and install the plugin, go to the “Options” tab and click the blue “Detect Orphan Options” button. The plugin will attempt to identify orphan options. It’s good, but not 100% accurate, so I can’t stress enough how important it is to back up the database before you delete any of these options.
Advanced Database Cleaner detected 1314 orphan options in the dorataya.com database. Most of them were left by EWWW Image Optimizer. Bad that so many options were left behind, but good that they were so clearly identified that I could remove them without fear. Apart from that, there were leftover options from several previously-installed themes and a miscellany from other plugins. And there were a few orphan options mistakenly assigned to existing plugins or to WordPress core.
If you’re not an expert, cleaning up your options table is kind of like editing your Windows registry. File it under “Kids, don’t try this at home.” Even if you sort of know what you’re doing, it’s better to leave a few supposedly-orphan options you aren’t sure of than to delete something that the plugin didn’t identify correctly. Be especially careful of deleting plugins that Advanced DB Cleaner assigns to WordPress Core.
I managed to slim the total options for Dorataya.com down from 1746 to 398. The three remaining “orphan” options actually belong to installed plugins, but have for some reason not been associated with them. This is a huge improvement.
Maintaining a Healthy WordPress Database
Once you’ve cleaned up your database (or had your developer clean it up for you), keeping it in good shape is fairly simple, unless you’re the type who installs and then uninstalls a lot of different themes and plugins. The WP-Optimize and WP Sweep plugins I mentioned above are a good addition to any site. Alternatively, you can use the optimization settings in ManageWP Orion, if you have several sites and want to manage them all in one place. You can also leave Advanced Database Cleaner installed and set it to run scheduled cleanups. The free version of the plugin is fine for routine, ongoing cleanup, but you need the paid version to clean up your options.
Very Usefull Article. Thank your For sharing