Last updated February 20, 2006. Created by EffieRover on February 19, 2006.
Log in to edit this page.

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);
}
?>

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.