PHP Optimising MySQL Tables

If you are working with large MySQL databases and are deleting, inserting or updating a large number of rows, then you probably want all the optimisation you can get, since moving a lot of data around can leave the database a little inefficient.

So, here is a nice little PHP script to "defrag" all your tables - it can cut execution time of SELECT statements down to half in many cases!

dbConnect()

$tables = mysql_query("SHOW TABLES");

while ($table = mysql_fetch_assoc($tables))
{
   foreach ($table as $db => $tableName)
   {
       mysql_query("OPTIMIZE TABLE '".$tableName."'")
       or die(mysql_error());
   }
}

The MySQL command for running this on just one table is simply:

OPTIMIZE TABLE 'tableName';

An easy trick that can really speed up a sluggish database driven website or application. Enjoy.

High Performance MySQL: Optimization, Backups, Replication, and More