Posted By

sdxxx on 02/03/11

Tagged

mysql

Versions (?)

Last Edited at 02/03/11 01:02am

Statistics

Viewed 525 times

Favorited by 0 user(s)

Related snippets

cronjob to optimize MySQL database

/ Published in: SQL

Cronjob and PHP examples below

Highlighted TextExpand

## 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.

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.