[NuAlpha]

This is a module to automatically optimize all of the tables in your database overnight through the use of a cronjob. Also performs repairs as needed.

First create a file in your Xcart root directory named: db_optimizer.php

Put the following inside that file:

<?php
##########################################################################
##                       ## Database Table Optimizer ##                 ##
##########################################################################
##                                                                      ##
## Optimize all database tables once every 24 hours through a cronjob.  ##
##########################################################################
## Version: 1.1.0 (6/8/2004)
## Last Updated: 7/13/2004

# Define Constants
define('ERROR_LOG', '/absolute-path-to-your-log-directory/db_optimization_errors.html');
define('DB_LINK', mysql_connect('server_address','sql_login','sql_password'));
define('DB_NAME', 'database_name');

# Get all tables in the database.
mysql_select_db(DB_NAME, DB_LINK);
$result = mysql_list_tables(DB_NAME);

# Begin table optimizations.
for ($i = 0; $i < mysql_num_rows($result); $i++) {
	$current_table = mysql_tablename($result, $i);
	$opt_table = mysql_query('OPTIMIZE TABLE '.$current_table,DB_LINK)
				 or error_log("Couldn't optimize the '$current_table' table!
".
				 			  "MySQL said: ".mysql_error()."
\n",3,ERROR_LOG);
	
	if (!$opt_table && preg_match("/'(\S+)\.(MYI|MYD)/",mysql_error(), $damaged_table)) {
		# Repair broken indexes.
		mysql_query('REPAIR TABLE '.$damaged_table[1],DB_LINK)
				 or error_log("Couldn't repair the '$current_table' table!
".
				 			  "MySQL said: ".mysql_error()."
\n",3,ERROR_LOG);
		$opt_table = null;
		# Try to optimize the table again.
		$opt_table = mysql_query('OPTIMIZE TABLE '.$current_table,DB_LINK)
				 or error_log("Couldn't optimize the '$current_table' table after trying to repair it!
".
				 			  "MySQL said: ".mysql_error()."
\n",3,ERROR_LOG);
	}
}
?>

Next create a cronjob that runs sometime each day whenever your site has the least amount of traffic.
Use the cron command: php /absolute-path-to-this-file/db_optimizer.php

Substitute the data in the constants section of this file with your specific information. Also replace absolute-path-to's with the appropriate path.

That's all there is to it. If there are any problems, let me know!