Talk In Code

You can optimize MySQL tables using the OPTIMIZE command. This can be used if you have a table with any variable length rows and you make lots of changes to the data in that table. Variable length columns are VARCHAR, VARBINARY, BLOB, or TEXT. The OPTIMIZE TABLE will defragment the data and reclaim any unused space. This also has the effect of resetting any auto incrementing numbers to the highest value in the table.

OPTIMIZE TABLE tbl_name;

You can do this for every table in the database, but to save time MySQL comes with a command line script that will optimize all tables in a database. This program is called mysqlcheck and can be run like this.

mysqlcheck -o database -u user -ppassword

Note that when writing this command you should not put a space in between the -p and your password.

The mysqlcheck program can also be used to check (-c/-m/-C), repair (-r) and analyze (-a) the tables in a database. These commands can be done by using the CHECK, REPAIR and ANALYZE MySQL commands.

Optimizing tables will clear out unnecessary settings and speed up your tables slightly, but there is little point in doing this more than once a month, or once a week for very high traffic MySQL servers.

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.