Cron to run every day to optimize mysql?

[tiff2342]

Is there a cron script i can run every day to flush the fragmented tables and improve mysql performance?

[howzit]

Yes, create a db_clean.php and set it up as a cronjob....

<?
include '../incl/config.php'; //// This should contain your DB connections
	// Set font
	echo "<font size=\"-1\" face=\"arial\">";
	// 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 sucessful (have permission to)
			If (mysql_select_db($db_row[0])) {
				// Echo database name
				echo "<br><b>";
				echo $db_row[0];
				echo "</b><br>";
				// 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 "&nbsp;&nbsp;<i>";
							echo $tbl_row[0];
							echo "</i><br>";
						} // End table while loop 
					} else {
						// Alert that there are no tables within database
							echo "&nbsp;&nbsp;<i>No Tables</i><br>";
					} // End table existance if statement
			} // End database if statement
		} // End database while loop
	// Alert them that operation was successful
	echo "<br><br><b>Above tables successfully optimized!</b>";
	// End font
	echo "</font>";
?>

[tiff2342]

does this do all the databases on the shared server and will remove fragmented cache?

[cPanelTristan]

The script is very carefully commented for each line provided. Did you read the script provided to see what it is doing? If you read each comment above each command, you can see what it does specifically.

[dmacleo]

The script is very carefully commented for each line provided. Did you read the script provided to see what it is doing? If you read each comment above each command, you can see what it does specifically.

// Select currently looped database and continue only if sucessful (have permission to)

thats what made me also wonder if it did all.

would a cron such as

0 1 * * * mysqlcheck -u root -p[YOURPASSWORD] --auto-repair --optimize --all-databases] > /dev/null

also do this?
seems the script is much more involved.

[cPanelTristan]

The script will only check tables that can be optimized and exist. It's far more detailed and would likely take less processing power than running through them all without checking.

It's up to you what you want to do, but it is definitely advised to always read the script comments to understand a script, since that's the purpose of having detailed comments.

[dmacleo]

for the record I wasn't denigrating the script, it looks powerful.
just wanted to clarify if it did all also.

[syndicated]

Looking over the script all it seems to do is optimize only

// Statement to optimize table
$opt_table = 'OPTIMIZE TABLE ' . $tbl_row[0];

That seems to be the only command on SQL it actually does other then search for and loop through dbases

[howzit]

dmacleo, in short...
Setting up this script as a cronjob to run at a specific time each day will optimize all db's on your server.

[syndicated]

The script will optimize, the command line will also repair and optimize.
One is PHP the other is Shell
One has customized reporting the other is default

When I run the command line it skips all optimized tables and only does ones needing it, goes very fast but gives me a list of all the skipped ones + ones that are done.