Moodle Database Optimization and Analysis");
// connect to the database server
$db = mysql_connect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass);
if (!$db) {
die ('Unable to connect to database!\n'.mysql_error());
} else {
echo ("Database connected successfully\n" . "
\n");
}
// select the moodle database
mysql_select_db($CFG->dbname,$db);
// get a list of tables for this database
$tablequery = "show tables like '".$$CFG->prefix."%'";
$tablelist = mysql_query($tablequery,$db);
echo ("Got list of tables OK" . "
\n");
// cycle through them for repair
while ($tar = mysql_fetch_array($tablelist))
{
$tablename = $tar[0];
// Check table indexes and attempt repair if needed
mtrace ("Now checking table $tablename ... ");
$repres = mysql_query("CHECK TABLE $tablename");
$result = mysql_fetch_array($repres);
mtrace ($result['Msg_text'] . "
\n");
if ($result['Msg_text'] <> "OK") {
//table has crashed so run a repair
mtrace ("Crashed table $tablename. Now attempting to repair indexes ...");
$repres = mysql_query("REPAIR TABLE $tablename QUICK");
$result = mysql_fetch_array($repres);
mtrace ($result['Msg_text'] . "
\n");
if ($result['Msg_text'] <> "OK") {
// Quick repair did not work, so try extended
mtrace ("Cannot quick repair $tablename. Now attempting full table repair ...");
$repres = mysql_query("REPAIR TABLE $tablename EXTENDED");
$result = mysql_fetch_array($repres);
mtrace ($result['Msg_text'] . "
\n");
if ($result['Msg_text'] <> "OK") {
// Extended repair did not work, so try use_frm
mtrace ("Cannot repair $tablename. Now attempting index rebuild ...");
$repres = mysql_query("REPAIR TABLE $tablename USE_FRM");
$result = mysql_fetch_array($repres);
mtrace ($result['Msg_text'] . "
\n");
}
}
}
// Optimize table
mtrace ("Now optimizing table $tablename ... ");
$repres = mysql_query("OPTIMIZE TABLE $tablename");
$result = mysql_fetch_array($repres);
mtrace ($result['Msg_text'] . "
\n");
// Analyze table
mtrace ("Now analyzing table $tablename ... ");
$repres = mysql_query("ANALYZE TABLE $tablename");
$result = mysql_fetch_array($repres);
mtrace ($result['Msg_text'] . "
\n");
}
mtrace ("All Moodle tables have been checked, analyzed and optimized");
?>