WordPress uses DB entries called _transients to cache certain data. Cached entries are by default, things like RSS info, when cron last run, etc. If you use a plugin like Google Analytics Dashboard (GAD hereafter) though, you also get cached data relating to that. Unfortunately, either due to a bug in WordPress, or something left out of GAD, this cached info doesn’t seem to be deleted after it’s designated expiration time/date (_transients have a set expiration time, but they don’t seem to matter for GAD set transients).
Looking around online, I found someone who was dealing with a similar problem here.
The trick was to add the following to my the functions.php file in my theme.
add_action( ‘wp_scheduled_delete’, ‘delete_expired_db_transients’ );
function delete_expired_db_transients() {
global $wpdb, $_wp_using_ext_object_cache;
if( $_wp_using_ext_object_cache )
return;
$time = isset ( $_SERVER[‘REQUEST_TIME’] ) ? (int)$_SERVER[‘REQUEST_TIME’] : time() ;
$expired = $wpdb->get_col( “SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE ‘_transient_timeout%’ AND option_value < {$time};" );
foreach( $expired as $transient ) {
$key = str_replace('_transient_timeout_', '', $transient);
delete_transient($key);
}
}
Later in the day, when that cron task ran, it removed all the expired transients from my wp-options table, and at midnight, when my system cron task ran to optimize my DB tables, the table shrunk down to the size it should be. If you so wanted, you could add the optimize operation to the above, but I figure it’s not that big of a deal once things are under control. As always, back up your DB and any files you modify before implementing changes. If you have access to a mysql console, you can also try the query out by doing something like:
SELECT option_name FROM wp_options WHERE option_name LIKE '_transient_timeout%' AND option_value < now();
You'll want to change "wp_options" if you have a different prefix set for wordpress. Theoretically, that query should show you all your expired transients.
Have fun!