Jul 11

Easy way to optimize all tables in mySQL with PHP

PHP, mySQL

I ran across a pretty old “hack” that grabs a list of all the tables and forces
them to optimize. Its generally a pretty useful script to schedule and run once
a week. For those of you playing the home game, you’ll need to tap into your own
database class or adjust it to use the normal db connect functions, but that’s really
simple to do.

1
2
3
4
5
6
7
8
9
10
11
12

 
$db = mm_database::getInstance(); //Build DBO
 
//Iterate through each table, optimizing it.
while ($table = mysql_fetch_assoc($db->query("SHOW TABLES", NO_CACHE, RESULT_FORMAT)))
{
   foreach ($table as $db => $tbl)
   {
       $db->query("OPTIMIZE TABLE '$tbl'", NO_CACHE);
 
   }
}