Optimisez - Compacter Tables MySQL
************************************************** *********************************************
Extrait de :
http://www.lxg.de/code/shell-script-...atabases-mysql
Script unix :
#!/bin/bash
MYSQL_LOGIN='-u root --password=YOURPASSWORD'
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN | grep -v Tables_in_)
echo "Switching to database $db"
for table in $TABLES
do
echo -n " * Optimizing table $table ... "
echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN >/dev/null
echo "done."
done
done
************************************************** *********************************************
Extrait de :
http://psoug.org/snippet/PHP-Optimiz...es-mySQL_6.htm
// 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 "
";
ECHO $db_row[0];
ECHO "";
// 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 "
";
ECHO $tbl_row[0];
ECHO "";
} // End table while loop
} ELSE {
// Alert that there are no tables within database
ECHO "
No Tables";
} // End table exists if statement
} // End database if statement
} // End database while loop
// Alert that operation was successful
ECHO "
Above tables successfully optimized.";
?>