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"); ?>