Last updated 20 February 2006. Created on 19 February 2006.
Edited by EffieRover. 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.

// 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 forums, or join #drupal-support in IRC.