If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

One of the results of fragmentation is that the tables take up more disk space than they ideally should consume if they were not fragmented. One of the more serious implications of fragmented tables is that if a table is sufficiently large, the extra time needed to read records from a fragmented table can cause intermittent timeouts.

One way to detect fragmentation is to use the MySQL tuner script mentioned in this article (coming soon). Another is to run the following SQL query from the shell or through phpMyAdmin:

SELECT TABLE_NAME,Data_free FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0;

If you find you have fragmented tables, proceed to the next section.

Optimizing MySQL tables is quite simple. Once you know the table name that needs optimizing, it's quite simple to perform:

sql> USE db_name;
sql> OPTIMIZE TABLE fragmented_table;

This can be very tedious however if you have multiple tables which you need to optimize. Below is a script that will automate the optimization process. It can be run from the shell by pasting the code into a file and running php filename.php.

<?php
// Modify these to suit your server
$host = "localhost";
$user = "root";
$pass = "rootpass";

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

$dummyDb = "mysql";

$dbLink = mysql_connect($host,$user,$pass);

$res = mysql_db_query($dummyDb, "SHOW DATABASES", $dbLink) or die("Could not connect: " . mysql_error());
echo "Found ". mysql_num_rows( $res ) . " databases\n";
$dbs = array();
while ( $rec = mysql_fetch_array($res) ) {
  $dbs [] = $rec [0];
}

$totalOptimized = 0;
foreach ( $dbs as $dbName ) {
  echo "Database : $dbName \n";
  $res = mysql_db_query($dummyDb, "SHOW TABLE STATUS FROM `" . $dbName . "`", $dbLink) or die('Query : ' . mysql_error());
  $toOptimize = array();
  while ( $rec = mysql_fetch_array($res) ) {
    if ( $rec['Data_free'] > 0 ) {
      $toOptimize [] = $rec['Name'];
      $totalOptimized++;
    }
  }
  if ( count ( $toOptimize ) > 0 ) {
    foreach ( $toOptimize as $tbl ) {
      echo "\tOptimizing $tbl.....";
      mysql_db_query($dbName, "OPTIMIZE TABLE `" . $tbl ."`", $dbLink );
      echo "Done.\n";
    }
  }
}

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$totalTime = round(($time - $start), 6);
echo "\nNumber of fragmented tables optimized: " . $totalOptimized;
echo "\nJob completed in " . $totalTime . " seconds\n";
?>

/* @todo * * http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html * http://www.mysqlperformanceblog.com/2009/11/05/innodb-look-after-fragmentation/ * */