How to shrink the size of wp_options table in WordPress

I was recently horrified to discover that the “wp_options” table of this site was over 1.3 gigabytes in size. For those of you counting at home – that is ENORMOUS and unnecessary. I was confused, because the table seemed to balloon overnight (and likely did balloon overnight, it turns out!).

The issue was caused by a poorly-coded plugin that added some HUGE rows to wp_options. I’m not 100% sure of the culprit nor the specific row, but I’m pretty sure it was either the Jetpack or Spectra plugin that did the ballooning. How do I know this? Well, a manual search (e.g. using phpMyAdmin and my eyeballs) of recent rows in the “wp_options” table turned up a lot of rows that had values that included both “jetpack” and “spectra”.

How I deleted the offending rows and shrunk my “wp_options” table from 1.3gb to 3mb

I used a pretty blunt hammer stroke to fix the issue, running the following 3 sql queries via phpMyAdmin:

  1. DELETE FROM wp_options where option_name like ‘%jetpack%’;
    This removed 180 rows.
  2. DELETE FROM wp_options where option_name like ‘%spectra%’;
    This removed 97 rows.
  3. DELETE FROM wp_options where option_name like ‘%transient%’;
    This removed 51 rows.

So, again, I’m not sure exactly which plugin was the offender, but deleting only 328 rows from my database shaved off over a gigabyte of girth.

Is this helpful?

Posted in

Toby Cryns

Toby Cryns is a freelance CTO, expert WordPress consultant, and teacher.

He offers free advice to improve your freelance biz.

He also publishes small droppings every now and then to twitter.com/tobycryns and twitter.com/themightymo

Follow Toby's contributions on Github and WP.org.