How to Cleanup the wp_options Table in WordPress

As you probably know, the WordPress database plays a critical role in the smooth functioning of your site. There are certain tables with queries that are performed on a regular basis and if they become slow or inefficient, your site can suffer as well. One such table in the database is called “wp_options”. It’s an area where a lot of plug-ins and themes store bits and pieces of information that help in rendering the page. Not all of them however properly cleanup after they have been deactivated and uninstalled. This means that you have a lot of residue of data left over in wp_options that will only grow over time. If you’ve had a blog for several years and have never bothered to clean up your wp_options table, now’s a good time to start.

In addition to plug-in specific data, we also have something known as “transients” which specifically have an expiration date but only if accessed after that date. If the plug-in is uninstalled earlier, those transients remain in the database forever. By properly cleaning up the wp_options table, you can reduce the strain on your site and improve the page generation speed. Here’s a step-by-step tutorial on how to go about it.

Finding Expired Data

To find out what was plug-ins have left traces in wp_options, we’re going to have to scan the table manually. There’s really no shortcut to this. So let’s get started by opening up our cPanel and navigating to the phpMyAdmin section to access our database as shown below.

Once inside, select your WordPress database from the left-hand side and then click the “SQL” tab as shown below as a preparation for entering a query. In the blank text field of the SQL tab, type in the following and hit “Go” on the bottom right-hand corner:

SELECT * FROM `wp_options` WHERE `autoload` = 'yes'

This will display all the rows in the wp_options table that are requested automatically for each page load. Due to the way phpMyAdmin is set up, it’ll show you the rows in batches of 30 and that makes it inconvenient to quickly scan them. So take a note of the total number of rows returned as shown in the screenshot below and input that value in the “Number of Rows” box and in the “Start row” field, type “0”. Now hit “Show”.

This will display all the auto loaded rows at once on the screen. If you have a huge wp_options table, it may take some time to load fully. Because of this, I recommend keeping this tab open for later reference and opening up a new one with the same phpMyAdmin SQL box. This way, you can quickly scan the main table in between deletions without having to reload the whole thing.

Now that you have wp_options in front of you, scroll down looking at the “option_name” table. If you’ve been the sole owner of the blog over the past few years, you’ll start seeing familiar names of plug-ins that you’ve installed and deleted a long time ago. What we want to do is find the most egregious violators with a large number of rows and delete those first. Unfortunately before writing this tutorial I already deleted quite a number of rows so what’s left isn’t all that significant. But here’s the way to go about it.

While scrolling down for example I see that the “Disqus” plug-in has left a lot of entries with the word “disqus” in them. So despite being uninstalled, all of these rows are still being auto loaded on each and every WordPress page. To find out exactly how many rows Disqus has left behind, you can simply use the browser search function to get an overall count as shown here:

This comes to only 11 rows, but before I had cleaned out wp_options, there were other plug-ins that had significantly more. In any case, I don’t want even those 11 rows of Disqus cluttering up my table. The first job is to isolate them so go to the SQL section in a new tab and type in the following:

SELECT * 
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%disqus%'

In this example of course I’m using “disqus” as the search term. If you’ve found an outdated plug-in of your own that using up a lot of space, replace the name within the two percentage sites. But make sure that it’s unique! You don’t want to delete unrelated rows by accident.

Once you run the above command, it’ll show you all of the rows belonging to the plug-in in question. I find this intermediate step is important because it shows you exactly what’s going to be deleted. So if any other rows match the criteria above, you’ll be able to see them and hopefully take corrective action before accidental deletion.

Once you know that these are the rows you want to remove, go back to the SQL tab and type in exactly the same query as above, only this time replace “SELECT *” with “DELETE”.

DELETE
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%disqus%'

Make sure that everything else remains the same. Remember that the database is an extremely crucial part of your WordPress installation and you shouldn’t mess around with it lightly. Exercise extreme caution when changing anything and always make sure that you have a backup before you start making modifications. Personally, since I use the SiteGround hosting service they take regular backups on my behalf so I’m not particularly worried.

When you run the above query, phpMyAdmin will ask you for confirmation in a pop-up dialog box. If you’re sure, go ahead and the necessary rows will be deleted.

Once that’s done, go back to the tab holding the first wp_options query and continue scanning “option_name” for old plug-in entries. Try and maximize the efficiency of your efforts by starting off with those that have a lot of rows. I was astounded by the number of old stuff that was just sitting around using space and slowing down my site.

Removing Transients

One extremely important category of data is transients as explained above. You’ll be able to identify them because they will have the word “transient” in their name. As above, do a quick browser search in the wp_options query tab to see how many rows contain the word “transient”. When I did this the first time, I found well over a hundred!

The procedure for deleting transients is much the same as the one outlined above. Use the following query to isolate them:

SELECT * 
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%'

And once displayed, simply make the change from “SELECT *” to “DELETE” and you should be good to go.

Even with all this cleaning, I still have a large number of old plug-ins using up small numbers of rows and it’ll take me a while to get rid of all of them. A good number to shoot for in the wp_options table with autoload=’yes’ is 200. Depending on the kind of site you have and the number of plug-ins, this may not be possible to achieve. But if you can get it to around that number, you’ve done a good enough job.