WordPress wp_options table size too Big, and how to reduce it!

///WordPress wp_options table size too Big, and how to reduce it!

WordPress wp_options table size too Big, and how to reduce it!

Recently one of our clients running WordPress with a bolt on Ecommerce plugin WP Ecommerce, was experiencing some issues with performance. We found the database had ballooned to 175mb, not overly big but huge for this particular client. So we looked at how to resolve and reduce the database size.

Upon investigating using PHP MyAdmin we found that the wp_options table was the main culprit 169mb. So to remedy this we first took a complete database backup before we started, in-case we needed to restore. After some further research we found this interesting blog post here, “Easily Reduce & Clean Size of Options Table in WordPress“, this lead us to our first step of running an SQL command to remove temporary entries that WordPress creates in the options table.  These entries are transient entries that are usually created to simply store cached information.

Step 1

So in PHP MyAdmin we ran the command:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient_%');

After running the command approximately 220,000 rows were removed! Success, well nearly. We could see the rows had reduced but the table size had not budged, we also noticed in MySQL that the overhead size for that table was also at 156mb.

Step 2

So with a little bit more research we found that because of the huge number of rows removed we needed to perform a little database maintenance. So we ran a repair and optimize command on the database in PHP MyAdmin and hey presto our database had reduced from 175mb to 5mb and all was working fine.

We hope this helps any other people running WordPress in a similar situation.


The following two tabs change content below.
2018-04-03T10:17:29+00:00 November 20th, 2013|WordPress|3 Comments

About the Author:

Young Cow Ltd
Young Cow Ltd offer strategic performance based Web Design & Internet Marketing services.


  1. Avatar

    Holy cow, this was excactly what I needed! I had over 500 MB of data in this bugger – almost all which was of the _transient_ type..
    (I have heard about people having the issue with the Nextgen Gallery too, adding some other line over and over, jut in case somebody else is searching and stumbling accross this post.)

  2. Avatar

    Life saver, database of my blog was filed with a chunk of transient option.

  3. Avatar

    If anyone is running WooCommerce then they can clear these directly from the dashboard. Woocommerce > System Status > Tools, “Clear transients”.

Leave A Comment

Paste your AdWords Remarketing code here