PHP: Optimize and Analyze All Your MySQL Tables CodeUnit 19 MAR 2010

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());

Simple, but effective.

About Craig Lotter

Software developer, husband and dad to two young ladies. Writer behind An Exploring South African. I don't have time for myself any more.