Home | About Chris Hope | Contact Chris Hope | Search:

Optimize tables in MySQL automatically with PHP

Posted September 7th, 2008 in MySql and PHP

In previous posts I looked at how to optimize a MySQL table from the MySQL command line interface and from phpMyAdmin by using the optimize [tablename] command to free up unused space. In this post I will look at how to do this with a PHP script which could be run periodically to optimise all non-optimal MySQL tables.

The SQL we'll use to find tables which are non-optimal looks like this:

SHOW TABLE STATUS WHERE Data_free > [integer value]

substituting [integer value] for an integer value, which is the free data space in bytes. This could be e.g. 102400 for tables with 100k of free space. This will then only return the tables which have more than 100k of free space.

An alternative way of searching would be to look for tables that have e.g. 10% of overhead free space by doing this:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1

The downside with this is that it would include small tables with very small amounts of free space so it could be combined with the first SQL query to only get tables with more than 10% overhead and more than 100k of free space:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400

Using the above SQL, the PHP code would look like this:

$res = mysql_query('
  SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400
');

while($row = mysql_fetch_assoc($res)) {
  mysql_query('OPTIMIZE TABLE ' . $row['Name']);
}

And that's all there is to it. You could then run this PHP code snippet within a full PHP script and run it via cron once per day.