WordPress Optimization: Clean Up Your Database

Optimizing WordPress is more than installing a caching or code-minimizing plugin, despite what the marketing would have you believe.

When was the last time you checked your WordPress hosting or cleaned up your database?

Clutter and bloat in your WordPress install is a major culprit for slowness or things going wrong and is down to a lack of routine housekeeping.

While there are some kind web hosts who will be more nuanced in how they deal with this, the incentives align more readily with the hosting company simply encouraging (or enforcing) an upgrade to a higher plan or a surcharge, rather than helping you reduce your resource usage.

This isn’t a pitch for my website care service or for me to go in and fix things for you, by the way, it is something any website owner can be on top of before it becomes a problem.

Before You Optimize and Delete Files, Make Sure Your Backups are Good

Before you start optimizing your WordPress, now is a good time to take a backup.

You do have a backup, right? And preferably not in the same host as your main website?

Personally, I like to have a live site, a staging site, daily backups, and a backup to my backup, at a minimum.

If you use something like LocalWP, you can keep a development copy locally, and keep that up to date, too.

Creating a local version is also a way to test your backups contain everything, too. You would be shocked how many people think they have a backup when they really don’t.

How to Optimize Your WordPress Data and Database

As your WordPress install gets older, it will naturally build up layers of sedimentary clutter.

Also, as you test out things, upgrade things, and swap out themes/plugins, some of that bloat will stick around, especially in your database and media library where it is out of site to an extent.

The usual suspects for over-stuffed websites are:

• Old backups and files remaining from migrations – Look for folders and .zip files with tell-tale names such as “backup”, “bak”, “migration” or “old”.
• Huge log files – There are some things that write to a log whenever something important happens, and they do not always tidy up afterward. Some of these logs will be text files, called something.log usually, for example, your error log. Others will be in the database, which causes your database to bloat up massively. Unfortunately, you might need to investigate which part of your site is the culprit before deleting, but a good place to start is anything that logs the emails you send from the site, login attempts, or the form messages you receive.
• POP3/IMAP Email Inboxes – Using your hosting email and storing all your email attachments there can very quickly bloat up your server usage because many email clients do not clear out after reading and downloading your mail. I prefer to have my email inbox on a dedicated system. For years I used Fastmail.fm, and I still have an account there, but for the last few years I have been using Gmail just because that way my wife and daughter get accounts, and because of the extra Google Drive and Google Meet space I pay for.
• Hosting overly large images, and duplicate images – My wife got caught out with this quite suddenly. Her workflow for her ecommerce store was to download her product photographs from her DSLR camera and upload them to her site. First off, this produces massively high-resolution images (even from a smartphone these days), and secondly, even more copies are then made because by default WordPress will produce different sizes.
• Self-hosting videos and big high-fidelity podcasts – Video and large, lossless audio, can be just as much of a space hog or even worse. If your videos are free to view and don’t require a login or payment, you might as well throw them up on YouTube. Otherwise, there are several video hosting services out there where you will get greater performance for not much cost, plus you can restrict the access to only those who should be allowed to see them.
• Unused plugins, themes, etc – Over time we tend to build up a plugin and theme graveyard on our sites, even though you can delete after deactivation. This can really add up, especially when the themes or plugins come with example content. For example if you install an LMS plugin, such as LifterLMS, you might set up the example courses that then put content into your database and media files into your uploads.
• Bloated, unsupervised databases – As mentioned earlier, adding logs to the database is a candidate for old and useless content clogging up your system, but database bloat can also be a symptom of things being broken now or in the past. Lots of plugins hammer your database with little thought to indexes, caching, and performance, so having extra unused data adds to the already bad speed. Sometimes you can’t avoid a large database, for example, an e-commerce store with lots of products, but in general terms, a bloated database is a slow database.

Speed Up Your WordPress Database

Speeding up your WordPress database depends largely on if you are noticing the speed problems on the front end, IE. when reading pages and posts, or when in the Admin.

On the front, you can largely mitigate a slow WordPress database using things like Cloudflare, caching, and even more recent PHP versions.

None of that will help in the backend for actually administrating the site, however.

Generally speaking, the more unnecessary data your WordPress install has in its database, the sooner you will notice a slowdown.

In particular, unnecessary WordPress options, especially those loaded automatically all the time, are going to bog down your site.

Joost has a great tool called AAA Optimizer for getting rid of unused WordPress options but be warned, it is also capable of really messing up your site.

What is the BEST Way to Optimize a WordPress Database?

Optimizing your WordPress database can be done in a few ways:

• Commercial and free plugins.
• Code snippets and command-line tools.
• Going into your MySQL database admin and cleaning your database.
• Exporting all your content and settings then starting a fresh install.

The quickest and easiest is the first option, and that also has a nice side-effect of being able to run the operations on a schedule.

That said, before you go ahead and automate, it is good to know what is going on and why your site or database might be getting bogged down in the first place.

Cleaning Up Your WordPress Database

Not only will cleaning up help you avoid being overcharged by your web host, but you might also speed up the visitor experience AND decluttering will make backups more likely to succeed.

Even better – for those of us in the Northern Hemisphere anyway, summer is a great time to do this work!

Get Table Sizes in Megabytes

If you have access to your database via PHPMyAdmin, you can run the following query to see where all your space is being used:

SELECT table_schema as Database, table_name AS Table,
round(((data_length + index_length) / 1024 / 1024), 2) Size in MB
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Manual WordPress Database Optimization Using MySQL

Found a table that is bloated and the data is unused but you still need the table?

You can just delete the contents but surprisingly that won’t free up any space right away.

Due to the weird way that MySQL and some other databases work, you need to do more than just delete the data if you want your space back. A straightforward approach is to recreate the table afresh, using the same structure but without the legacy information.

1. Delete Everything in the Table

TRUNCATE TABLE email_logs;

2. Create a New Table Based on the Original

CREATE TABLE email_logs_new like email_logs;

3. Drop the Original table.

DROP TABLE email_logs;

4. Rename the New Table to Match the Original

RENAME TABLE email_logs_new to email_logs;

Database Optimization Plugins

Going into the database and running queries is not for everyone (obviously good for automation, less so if you are unfamiliar with SQL programming).

A couple of decent plugins that do a lot of what you need are WP Optimize, which is also a nice tool for speeding up, image compression, and caching, and Database Cleaner which focuses purely on the database side of things.

Both have free and paid options and of course, my clients get access to my agency license for WP Optimize.

Clean up the WordPress Database:

Go to WP Optimize > Database.

Open the Tables menu. You’ll see tables with a “Remove” button. These are from deleted plugins. Click “Remove,” confirm the action, and delete all unwanted tables.

How to Optimize the WordPress Database:

• Go to WP Optimize > Optimizations.
• Select all and click “Run selected optimizations.” This might take some time depending on your site size.

Schedule Your WP Database Cleanups:

• Go to Settings in WP Optimize.
• Enable scheduled cleanups (weekly, daily, fortnightly, or monthly).
• Choose what to optimize or clean. Keep at least 2-3 post revisions to restore content if needed.

What is Next?

With a cleaner database, you should see a noticeable speed improvement when browsing your admin area, and you might also see a speed-up on the front end, especially with anything dynamic or personalized.

Next up I will go over how to clean up your images, media, and files.