PHP Cron Snippet
This snippet, added to your cron task, optimizes any table that has 10% or more free space. In MySQL, this also repairs any damaged records and reindexes, so it's a good thing to do periodically -- but not every single time you run cron.php. This snippet makes a judgement call based on amount of free space.
Note that it does not die if the queries fail ... I really should update this to mail the user if that happens.
<?php
// optimize tables if needed; mysql specific
$result = db_query('SHOW TABLE STATUS');
while ($table = db_fetch_object($result)) {
$overhead = ($table->Data_length>0) ? $table->Data_free/$table->Data_length*100 : 0;
if ($overhead > 10) {
$optimize = db_fetch_object(db_query('OPTIMIZE TABLE ' . $table->Name));
$tablelist .= "$table->Name\t$overhead OPTIMIZED $optimize->Msg_text\n";
}
}
if ($tablelist) {
mail('your.address@here','DRU: Optimize', $tablelist);
}
?>