Optimize All MySQL Tables PHP

Tuesday, October 9, 2012


If you looking for optimizing MySQL database tables from PHP dynamically, here is a solution to optimize entire tables a database.

Optimizing Tables we need to get Entire table list using "SHOW TABLES" query. as this return entire table names as record, after getting executed of this command call OPTIMIZE TABLE and REPAIR TABLE as per the table needs.

Primary Optimization will failed if the table been damage or corrupt. we will apply REPAIR TABLE

<?php

mysql_connect("localhost","root","");
mysql_select_db("your_db_name");


echo "<br /> OPTIMIZING TABLES ";

$alltables = mysql_query("SHOW TABLES");

// Process all tables.
while ($table = mysql_fetch_assoc($alltables))
{
 foreach ($table as $db => $tablename)
 {
 // Optimize them!
 if(mysql_query("OPTIMIZE TABLE ".$tablename))
  echo "<br>OK Optimized : ".$tablename;
 else
 {
  echo "<br>Error Optimizing Applying Reapir... ".$tablename;
  // Apply Reapirt if Optimization Failed;
  if(mysql_query("REPAIR TABLE ".$tablename))
  echo "Repaired !";
  else
  echo "Error Repairing Table!";
 }
 }
}
mysql_close();
?>




No comments:

Post a Comment