Lee's Weblog

Optimizing your database tables is an essential task and the performance results might amaze you. Optimization is essentially the process of defragmenting your database tables. Then you use variable-length database fields such as VARCHAR, BLOB, TEXT, or VARBINARY, defragmentation can occur when rows are deleted. 

Unfortunatley, there is no “OPTIMIZE DATABASE XYZ”, instead you need to go through your tables one-by-one and optimize them using an “OPTIMIZE TABLE XYZ”.

/* optimizeTables function
*
* Input: MySQL handle, database name
*/
function optimizeTables($conn, $database) {
mysql_select_db($database, $conn);

//Get a Table List
$TableQuery = "SHOW TABLES";
$rs = mysql_query($TableQuery);

//Optimize each table in the list
while($table = mysql_fetch_array($rs)) {
$OptimizationSQL = "OPTIMIZE TABLE " . $table[0];
$rs2 = mysql_query($OptimizationSQL);
$results = mysql_fetch_row($rs2);
echo $results[0] . "   " . $results[3] . PHP_EOL;
}

//Flush table cache and close the connection
mysql_query("FLUSH TABLES");
mysql_close($conn);
}

You can also execute `myisamchk` with appropriate options from the command like for each table. In my case, I wanted to be able to integrate this into another package which has its own crontab. One more caveat: If you are using InnoDB, this process will actually increase disk useage while freeing unused space. Not sure what to do about that one…but I dont use InnoDB…