25.8. How to proceed when MySQL is slow

It is of course too general a complain to say “MySQL is slow! What is wrong with my pages?”, but nonetheless, there exist a couple of things you can check if this happens to you (see slow page load and mysql errors):

• First obvious question: did your host upgrade or make changes to MySQL?

• Second, any new or modified modules added?

• Third, does your host have 'log slow queries' enabled for mysql so you can isolate which ones are causing the problems? When started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL commands that took more than long_query_time seconds to execute. See The Slow Query Log.

• Does this happen randomly or during certain time periods throughout the day?

• Maybe you need to rebuild your indexes. To rebuild your indexes, the easiest way to do so for a MyISAM table is to use the following statement, which rebuilds the index file:

  mysql> REPAIR TABLE tbl_name QUICK;

  If you don't have access to the MySQL prompt through a terminal, you can write a small PHP script that does the same. Copy the following code into a file and save it as, say, repair.php:

  <?php require("config.php"); $host = $dbhost; $database = $dbname; $username = $dbuname; $password = $dbpass; mysql_connect($host, $username, $password); @mysql_select_db($database); $res = mysql_query("repair table ".$prefix."_XXXX"); if ($res) { echo $res; } else { print (mysql_error()); } ?>

  Dont't forget to replace “XXXX” with the actual name (without the prefix!) of the table you are suspecting of causing you trouble. Read the table definitions in the nuke.sql file that came with your PHP-Nuke package under the sql directory (you used nuke.sql during installation to populate the database, see Section 3.2.4 and Section 3.4.5).

  Upload repair.php to the same directory on your web server where also config.php is in - and point your browser to it. Of course, you should delete it after you are done.

• The phpBB software used for the standard Forum in PHP-Nuke, comes with a table, nuke_bbsearch_wordlist, that is used to store words that occur in forum posts. Due to the large size of your database, the nuke_bbsearch_wordlist table may have grown very large. By default phpBB does not include an index for that table. Lack of an index on nuke_bbsearch_wordlist can make your forum slow down. Running the SQL command below will increase the size of the nuke_bbsearch_wordlist table (could double it), but a performance increase is expected with the index (see How to Increase your Forums Performance):

  ALTER TABLE nuke_bbsearch_wordmatch ADD INDEX post_id (post_id);

  Run this during a time on your site when it is least busy. More records on that table means longer run time.

  If you find that this doesn't work for you, you can remove the index with:

  ALTER TABLE nuke_search_wordmatch DROP INDEX post_id;

	Large Slow Query Logs
	The slow query log can be used to find queries that take a long time to execute and are thus candidates for optimization . With a large log, that can become a difficult task. You can pipe the slow query log through the mysqldumpslow command to get a summary of the queries which appear in the log.