How to Optimize (Clean Up) Your WordPress Database

If WordPress websites were bodies, think about their databases as the brains. A site’s database is your site’s memory. It is what stores all your information—posts, pages, comments, tags, categories, site usernames – everything. Without it, you have nothing. NOTHING.

That is why it’s incredibly important that you back up your database often, and show your clients how to do the same. You want to always have the latest and greatest copy of your website.

Beyond backing up your database, though, you want to also optimize it to its lightest and best shape. 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. Things like spam comments and the revisions you make to your blog posts need to be purged from WordPress from time to time. This junk can make the size of your database balloon, and a massive database can slow down your site because it has so much info to recall.

This tutorial will show you how to optimize a database using two tools: the WP-Cleanup plugin and phpMyAdmin.

Before you do ANYTHING involving your database, please back it up. Things could go awry, and you want to make sure you can put things back as you found them. Listen to me. This is important.

Now, let’s get started.

Download the WP-Cleanup plugin

In your WordPress dashboard, go to “Plugins” and then click “Add New.” Do a search for “WP-Cleanup.” Install and activate the plugin. Once this is done, click on “Settings” on the left sidebar of your dashboard. “WP-Cleanup” should now be an option. Click on it.

You will be taken to a screen that shows you your current database size, the percent of it that is useful data, and the percent that is post revisions, spam comments and other data you don’t need. In an easy-to-understand pie chart, you will see how much of your database is just extra fat you need to trim.

This chart shows that my database only has .37% waste, but it’s up to 126847 KB, which is about 126 MB. Because I KNOW my database isn’t supposed to be so big, I understand that I have way more junk than is visible here.

WP-Cleanup detects most junk, but it doesn’t detect what’s in the wp_commentsmeta table. This is why you also need to use phpMyAdmin in your cleanup.

Go into PhpMyAdmin through your host’s control panel

phpMyAdmin is software that lets you manage your database(s). It’s what most WordPress hosts use.

If you’re using WordPress.org, then you have a host that is holding the information of your website (yes, database, files all that) on a server. You need to access the control panel (aka cPanel) of your host to get to the phpMyAdmin. It is usually in the section that says “database” or “database servers.”

When you enter phpMyAdmin, you will see a dashboard that resembles this one.

WARNING: This dashboard gives you direct access to your database. BE CAREFUL. Because you see that word “Drop” with the red STOP sign next to it. That STOP sign is to let you know that if you hit that “Drop,” you will delete whatever data is in that table. Do not touch.

Under the “Table” column, you’ll see the names of the different segments of your database. In the “Rows” column is the number of actual rows of data in that table. And you can see how big each row of data is in the “Size” column. As you see, my database was 124 MB, just as I estimated based on what WP-Cleanup told me. And the biggest table is my wp_commentmeta, at 83.7 MB. WHOA!!!

The wp_commentmeta table is where comment history is basically stored, so it includes spam and other things your site does not need to hold on to. This table is a hoarder. If you have the Akismet anti-spam plugin, there’s buildup of that, too.

We must clean it up.

Clean up the WP_commentsmeta table

phpMyadmin uses SQL commands to manage the database functions. In the tab next to “Structure” you will see “SQL.” Click that.

You want to tell it to find any extra information (that is, any comments not related to the wp_comments table, which holds approved comments). This will find junk entries for you.

To first see how many junk rows you’ve got, use this command (just copy and paste it into the box).

SELECT * FROM wp_commentmeta WHERE comment_id 
NOT IN ( 
SELECT comment_id 
FROM wp_comments 
)  

Once you do, hit “Go.” It told me I had over 55,000 rows of junk. SHEESH!

Now that I knew where it was, I could delete all the junk. Go back to the SQL tab and use this command.

DELETE FROM wp_commentmeta WHERE comment_id 
NOT IN ( 
SELECT comment_id 
FROM wp_comments 
) 

When I ran this command, here’s what happened next:

WOWSIES! BE GONE, waste!

Optimize your database tables

After you’ve purged, the database is still sort of holding on to overhead, which is the remnants of the junk. Now, you just need to go back to the “Structure” tab and check all your tables. In the dropdown box under the tables, hit “Optimize Tables” and the database will do what it needs to for the refresh.

BOOM! My database shrank to 39 MB—I got rid of 85 megabytes of junk! The wp_commentmeta table now stood at a clean 31 rows of data, instead of the 60,000 it started at.

I checked my site to make sure nothing went awry, and it looked perfectly fine. VICTORY! You can jump out of phpMyAdmin now. Your work here is done.

WAIT. Actually, back up your database—because at the moment, your DB is squeaky clean.

Use WP-Cleanup to remove revisions

Now go back into WP-Cleanup in your WordPress dashboard. Check all the boxes and hit “Clean up selected items” just for good measure.

Do monthly optimizations of your database through phpMyAdmin, but do weekly quick purges with WP-Cleanup. Using both of them, you’ll be making sure that your database stays lean and uncluttered.

Pin the image below!

GoDaddy Managed WordPress is built from the ground up for maximum performance, security and ease of use. Click hereto experience the difference.