Return to Snippet

Revision: 40495
at February 3, 2011 01:04 by sdxxx


Updated Code
## CODE

mysql -u<username> -p<password> <database name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;"

## NOTE: Something like this may help. Dan W  made this script from his own
## knowledge, and a little bit of research on Google. This script can only 
## optimize one database. If you need to optimize more than one, you will 
## have to add a few more lines of code in there.

## CODE

<?php

$server = 'localhost';
$username = 'mysql_username';
$password = 'mysql_password';
$database = 'mysql_database_name';

### connects to the database, or dies with error
$connection = mysql_connect($server,$username,$password);
if (!$connection)
{
  die( mysql_error() );
}

### selects the db of choice, or dies with error
$db_selection = mysql_select_db($database, $connection);
if (!$db_selection)
{
  die( mysql_error() );
}

### selects all tables in the db of choice, or dies with error
$alltables = mysql_query("SHOW TABLES") or die ( mysql_error() );

### loops through all of the tables and optimizes each, or dies with error
while ( $table = mysql_fetch_array($alltables) )
{
  mysql_query("OPTIMIZE TABLE `".$table."`") or die( mysql_error() );
}

### closes the mysql connection
mysql_close($connection);

?>

## Be sure to edit the variables at the top to the values that are 
## appropriate for you. When you go to run your cron job, again just 
## do "php /full/path/to/script.php" and since optimizing tables may 
## lock down your DB for a second, run the script at a time when you 
## don't expect traffic.

Revision: 40494
at February 3, 2011 01:02 by sdxxx


Initial Code
##CODE

mysql -u<username> -p<password> <database name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;"

##NOTE: Something like this may help. Dan W  made this script from his own knowledge, and a little bit of research on Google. This script can only optimize one database. If you need to optimize more than one, you will have to add a few more lines of code in there.

CODE

<?php

$server = 'localhost';
$username = 'mysql_username';
$password = 'mysql_password';
$database = 'mysql_database_name';

### connects to the database, or dies with error
$connection = mysql_connect($server,$username,$password);
if (!$connection)
{
  die( mysql_error() );
}

### selects the db of choice, or dies with error
$db_selection = mysql_select_db($database, $connection);
if (!$db_selection)
{
  die( mysql_error() );
}

### selects all tables in the db of choice, or dies with error
$alltables = mysql_query("SHOW TABLES") or die ( mysql_error() );

### loops through all of the tables and optimizes each, or dies with error
while ( $table = mysql_fetch_array($alltables) )
{
  mysql_query("OPTIMIZE TABLE `".$table."`") or die( mysql_error() );
}

### closes the mysql connection
mysql_close($connection);

?>

Be sure to edit the variables at the top to the values that are appropriate for you. When you go to run your cron job, again just do "php /full/path/to/script.php" and since optimizing tables may lock down your DB for a second, run the script at a time when you don't expect traffic.

Initial URL
http://forums.asmallorange.com/topic/12948-cron-job-to-optimize-database/

Initial Description
Cronjob and PHP examples below

Initial Title
cronjob to optimize MySQL database

Initial Tags
mysql

Initial Language
SQL