• Skip to primary navigation
  • Skip to footer navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

WP Fangirl

WordPress Consultant Sallie Goetsch

  • speakerdeck icon
  • Home
  • About
  • Why WordPress?
  • How I Work
  • Portfolio
  • Services
  • Blog
  • Contact

Is Your WordPress Database a Disaster? Get a grip on your tables and options to improve performance

May 19, 2017 by Sallie Goetsch 1 Comment

This entry is part 2 of 4 in the series Checking Up on Your Website
House destroyed by an earthquake

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.

Backing up the database with BackupBuddy

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.

screenshot of WP-Optimize
WP-Optimize. Potentially risky optimizations are shown in red below this list.

 

Screenshot of WP-Sweep's post sweep
WP-Sweep breaks its optimizations down into Post, Comment, User, Term, Option, and Database.

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.

Screenshot of WordPress database in Adminer
Adminer looks a bit different from phpMyAdmin, but works similarly.

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.

WordPress database tables for Mergebot test site
Mergebot adds three tables to the WordPress database

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.

Database tables for Dorataya.com before cleanup
This database has 13 tables that we’re no longer using. That’s a lot of clutter!

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.

screenshot of tables cleanup in Advanced Database Cleaner
Click the blue button to find out which tables go with each plugin–and which have been left behind.

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

phpMyAdmin prompt to confirm drop tables
phpMyAdmin gives you this warning for a reason. There’s no undo.

Once you’ve identified orphan database tables, removing them is easy.

  1. Back up your database before you make any changes.
  2. Check the boxes next to the tables you want to remove.
  3. Scroll down to the bottom of the database and choose “Drop.” (This is a button in Adminer and a drop-down selection in phpMyAdmin.)
  4. Confirm that you really want to drop the tables.
  5. Check to make sure the website still functions.

Removing WordPress Users

User accounts on EastBayWP.com
The pared-down list of user accounts on EastBayWP.com

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.

Screenshot of Advanced Database Cleaner's options panel
Click the blue “detect orphan options” button to identify the orphans.

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.

Updraft Plus backup of WordPress database before cleaning up options with Advanced Database Cleaner

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.

Screenshot of Advanced Database Cleaner's orphan option detection results on dorataya.com
That’s more orphan options than options from active plugins and themes–not what you want at all!

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.

Screenshot of options table in Advanced Database Cleaner after deleting orphan options: only 408 options remain
Thanks to the Advanced Database Cleaner plugin, we’ve reduced our options table to 398 rows.

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.

Checking Up on Your Website Series Navigation<< Previous PostNext Post >>
Is Your WordPress Database a Disaster? Get a grip on your tables and options to improve performance

Related Items

  • backup in progress
    WordPress Database Backup
  • How to Repair and Optimize Your Database in WordPress 2.9
  • Database check and repair tools in cPanel
    Checking and Repairing Your WordPress Database

Share this post:

Share on Twitter Share on Facebook Share on Pinterest Share on LinkedIn Share on Email

Filed Under: Using WordPress Tagged With: Site Management, Tutorial, WordPress Database

Reader Interactions

Comments

  1. Jack says

    June 6, 2017 at 9:25 am

    Very Usefull Article. Thank your For sharing

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

What I Write About

  • Book Reviews
  • Content Strategy
  • Design
  • Hosting and Servers
  • Most Valuable Plugins
  • There's a Plugin for That
  • Using WordPress
  • Widgets
  • WordPress Consulting
  • WordPress Events

Series

  • Interviews (5)
  • Checking Up on Your Website (4)
  • Client from Hell (5)
  • WordCamps (17)
  • WP-Tonic Roundtable (30)
  • Modern Tribe Tutorials (13)


More in this series:

  • Are You Sure Your Website Is Still Working?
  • Is Your WordPress Database a Disaster?
  • It Takes More than HTTPS to Make Your Website Secure
  • Should You Give Your Website a Bonus…or a Pink Slip?

RSS Latest News from the East Bay WordPress Meetup

  • Does It Work? Using The New CSS Layout with Rachel Andrew
    Things change rapidly in the WordPress world. The content in this post is more than a year old and may no longer represent best practices.Description Over the past two years, […] The post Does It Work? Using The New CSS Layout with Rachel Andrew appeared first on East Bay WordPress Meetup.
  • Speaker Training
    Get the workbook and slides for the October 2019 speaker training, plus background and pro tips. The post Speaker Training appeared first on East Bay WordPress Meetup.
  • SEO Audit Template & Resources
    Our November speaker, John Locke, graciously provided a template for an SEO audit report. You can download it as a Microsoft Word or PDF document. The post SEO Audit Template & Resources appeared first on East Bay WordPress Meetup.

Footer

Contact Info

2063 Main St #133 · Oakley, CA 94561

+1 (510) 969-9947

author-izer

sallie [at] wpfangirl [dot] com

Location

Map of East Contra Costa County

I live in Oakley, CA and run a WordPress Meetup in Oakland, CA. Don't confuse them!

Subscribe for New Posts

  • Since I blog on an unpredictable schedule, you might want to subscribe by email. I'll also send out occasional announcements about events.

  • Privacy Policy: I will never sell or rent your contact information.

  • This field is for validation purposes and should be left unchanged.
  • Contact
  • Colophon
  • Comment Policy
  • Privacy Policy
  • Five for the Future

Copyright © 2023 · Utility Pro on Genesis Framework · WordPress · Log in

MENU
  • Home
  • About
  • Why WordPress?
  • How I Work
  • Portfolio
  • Services
  • Blog
  • Contact