Optimize A MySQL Table Using PHP

In MySQL the OPTIMIZE TABLE can be used if you have made changes or have deleted large parts of the table.

Any deleted rows are kept behind the scenes in the server in order to allow the reuse of these spaces. The OPTIMIZE TABLE command reclaims the unused space and defragments the data file.

For a normal MyISAM table the OPTIMIZE command works in the following way.

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

You can do this automatically by using the following PHP code.

// connect to database


$con = mysql_connect("localhost","root","wibble");


// select the correct database


mysql_select_db("database");


// get a list of the tables


$alltables = mysql_query("SHOW TABLES;");





// record the output


$output = array();





while($table = mysql_fetch_assoc($alltables)){


foreach($table as $db => $tablename){


$sql = 'OPTIMIZE TABLE '.$tablename.';';


$response = mysql_query($sql) or die(mysql_error());


$output[] = mysql_fetch_assoc($response);


};


};


// print output


print_r($output);

Here is a sample of what the output array contains

[0] => Array


(


[Table] => database.table1


[Op] => optimize


[Msg_type] => status


[Msg_text] => Table is already up to date


)





[1] => Array


(


[Table] => database.table2


[Op] => optimize


[Msg_type] => status


[Msg_text] => OK


)

Note that you don't need to run this command every time you do anything. You should only run this after a major data upheaval, or after a few months or weeks of usage.