20090910-MySQL-logoWhen you start working with sizeable datasets and the like, things like database optimization become more and more important for you to pay attention to.

So for today’s quick tutorial I present to you a script which can be used as either a cron or scheduled task and will run MySQL’s nifty analyze and optimize functions against all the tables in your database. Looking at it, you’ll see it is a pretty simple affair, basically we connect to our database in the usual fashion (note I’m simply using a connect function of my own design here) and then run a query to return the list of all tables in the selected database (again obscured by the connect function).

Once you have the list, it is a simple matter of looping through all the tables and executing the ANALYZE and OPTIMIZE statements against each table.

$conn = createconnection();

$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       mysql_query("ANALYZE TABLE `".$tablename."`")
       or die(mysql_error());
       mysql_query("OPTIMIZE TABLE `".$tablename."`")
       or die(mysql_error());
   }
}
closeconnection($conn);

Simple, but effective.