Optimize your Tables!

One thing that seems to always irritate me when im trying to organize my backups together is how the file sizes bloat over time. You can, and probably should look into optimizing your tables but for those of you who dont have the time or the will power to care to do it in any other method if you place this function at the top of your automated backup script and simply call it appropriately you will be able to automate the process of optimizing all tables that dont meet your threshold and or maximum free space requirements.

You can just set up a cron job to call your PHP scripts and it should take care of things from there.

$_CONFIG['threshold'] = 0.01;
$_CONFIG['maxfreedata'] = 102400;

function optimizeDatabase($host, $user, $pass, $dbase)
{
	global $_CONFIG;

	$mysqli = new mysqli($host, $user, $pass, $dbase);

	if (mysqli_connect_errno())
	{
		printf("Connect failed: %sn", mysqli_connect_error());
		exit();
	}

	if ($result = $mysqli->query('SHOW TABLE STATUS WHERE '.
		'(Data_free / Data_length) > ' . $_CONFIG['threshold'] .
		' AND Data_free > ' . $_CONFIG['maxfreedata']))
	{
		print 'Optimizing Database: ' . $dbase . "n";
		while ($row = $result->fetch_object())
		{
			printf("%-25s %8d %8dn", $row->Name,
				$row->Data_free, $row->Data_length);
			$mysqli->query('OPTIMIZE TABLE ' . $row->Name);
		}

		$result->close();
	}
	$mysqli->close();
}

optimizeDatabase('localhost', 'User', 'Password', 'Database');

One thought on “Optimize your Tables!”

  1. Good stuff man. I like this. I’ll have to keep this in mind when I work on PHP projects–really any MySQL project for that matter.

Leave a Reply