wp_options cleanser

gistfile1.php

<?php
/**
* Remove expired transients from the wp_options table.
* This script cycles through a number of expired transients until it doesn't find anymore.
* Our wp_options tables has over 2 million stale entries at time of writing, this limiting is really needed
* @author Skye Book
*/

$user = "";
$pass = "";
$host = "";
$dbName = "";

$cycle = 500;

$dsn = "mysql:host=".$host.";dbname=".$dbName;
$db = new PDO($dsn, $user, $pass);

$hadResponses = true;

// Safe hash lengths: 32, 13

$deleteCount = 0;

while($hadResponses){
	$localDeleteCount = 0;
	$hadResponses = false;
	$getOptions = "SELECT option_name FROM wp_options WHERE option_name LIKE '_transient_timeout%' AND option_value < NOW() LIMIT " . $cycle;
	try{
		$stmt = $db->prepare($getOptions);
		$stmt->execute();
		while($row = $stmt->fetch()){
			// Get the transient hash
			$optionName = $row['option_name'];
			$position = strrpos($optionName, '_');
			$hash = substr($optionName, ($position+1));
			//echo("At position: ".$hash."\n");

			// Check if the string is of a known hash length (There are some values like "categories", "tags", and "info" that still come up)
			$length = strlen($hash);
			if($length == 32 || $length == 13){
				$deleteMatching = "DELETE FROM wp_options WHERE option_name LIKE '%_".$hash."'";
				$deleteStmt = $db->prepare($deleteMatching);
				$deleteStmt->execute();
				//echo $deleteMatching;
				$localDeleteCount+=2;
			}
		}

		$deleteCount += $localDeleteCount;
		$hadResponses = true;
		echo $localDeleteCount." entries deleted in this run";
	}catch(PDOException $e){
		echo'exception';
	}
}

echo $deleteCount." entries deleted in total";
?>