Snippet Name: Optimize All Database Tables (mySQL)
Description: Short but useful script that optimizes all tables in a mySQL database.
Comment: (none)
Language: PHP, MYSQL
Highlight Mode: PHP
Last Modified: February 26th, 2009
|
<?
// Define mySQL variables
$hostname="host";
$username="user";
$password="pass";
// Connect to mySQL
mysql_connect($hostname, $username, $password);
// Statement to select the databases
$db_select = 'SHOW DATABASES';
// Query mySQL for the results
$db_result = mysql_query($db_select);
// Loop through all the databases
WHILE ($db_row = mysql_fetch_array($db_result)) {
// Select currently looped database and continue only if successful
IF (mysql_select_db($db_row[0])) {
// Echo database name
ECHO "<br><b>";
ECHO $db_row[0];
ECHO "</b><br>";
// Statement to select the tables in the currently looped database
$tbl_status = 'SHOW TABLE STATUS FROM ' . $db_row[0];
// Query mySQL for the results
$tbl_result = mysql_query($tbl_status);
// Check to see if any tables exist within database
IF(mysql_num_rows($tbl_result)) {
// Loop through all the tables
WHILE ($tbl_row = mysql_fetch_array($tbl_result)) {
// Statement to optimize table
$opt_table = 'OPTIMIZE TABLE ' . $tbl_row[0];
// Query mySQL to optimize currently looped table
$opt_result = mysql_query($opt_table);
// Echo table name
ECHO "Â Â <i>";
ECHO $tbl_row[0];
ECHO "</i><br>";
} // End table while loop
} ELSE {
// Alert that there are no tables within database
ECHO "Â Â <i>No Tables</i><br>";
} // End table exists if statement
} // End database if statement
} // End database while loop
// Alert that operation was successful
ECHO "<br><br><b>Above tables successfully optimized.</b>";
?> |
