Optimize your DB from PHP

This is a functionality I’ve been using in Open Classifieds since the 1.6.1. version.

It currently works for MySam working as follows:

• If the table has deleted or split rows, repair the table.
• If the index pages are not sorted, sort them.
• If the table’s statistics are not up to date, update them.

And for InnoDB, rebuilds the table to update index statistics and free unused space.

And if we want to do this in PHP for all the tables in your DB simply do this:

    $result  = mysql_query('SHOW TABLE STATUS FROM '. DB_NAME);
    while ($row = mysql_fetch_array($result)) $tables[]=$row[0];
    $tables=implode(", ",$tables);
    mysql_query('OPTIMIZE TABLE '.$tables);
    echo "
Optimized all the tables found in the database: $tables
";

That’s all! simple yet effective 😉