OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLEtbl_name[,tbl_name] ...
OPTIMIZE TABLE should be used if you have
deleted a large part of a table or if you have made many
changes to a table with variable-length rows (tables that have
VARCHAR, VARBINARY,
BLOB, or TEXT columns).
Deleted rows are maintained in a linked list and subsequent
INSERT operations reuse old row positions.
You can use OPTIMIZE TABLE to reclaim the
unused space and to defragment the data file.
This statement requires SELECT and
INSERT privileges for the table.
In most setups, you need not run OPTIMIZE
TABLE at all. Even if you do a lot of updates to
variable-length rows, it is not likely that you need to do
this more than once a week or month and only on certain
tables.
OPTIMIZE TABLE works
only for MyISAM,
InnoDB, and (as of MySQL 5.0.16)
ARCHIVE tables. It does
not work for tables created using any
other storage engine.
For MyISAM tables, OPTIMIZE
TABLE works as follows:
If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
For BDB tables, OPTIMIZE
TABLE currently is mapped to ANALYZE
TABLE. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.
For InnoDB tables, OPTIMIZE
TABLE is mapped to ALTER TABLE,
which rebuilds the table to update index statistics and free
unused space in the clustered index.
You can make OPTIMIZE TABLE work on other
storage engines by starting mysqld with the
--skip-new or --safe-mode
option. In this case, OPTIMIZE TABLE is
just mapped to ALTER TABLE.
OPTIMIZE TABLE returns a result set with
the following columns:
| Column | Value |
Table |
The table name |
Op |
Always optimize
|
Msg_type |
One of status, error,
info, or warning
|
Msg_text |
The message |
Note that MySQL locks the table during the time
OPTIMIZE TABLE is running.
OPTIMIZE TABLE statements are written to
the binary log unless the optional
NO_WRITE_TO_BINLOG keyword(or its alias
LOCAL) is used. This is done so that
OPTIMIZE TABLE statements used on a MySQL
server acting as a replication master will be replicated by
default to the replication slave.
OPTIMIZE TABLE does not sort R-tree
indexes, such as spatial indexes on POINT
columns. (Bug#23578)
User Comments
myisamchk --quick --check-only-changed --sort-index --analyze
do a myisamchk on the table.
--
notice the deleted blocks in the right hand corner of the dialog. The stat still indicates a number > 0 for tables with deleted blocks.
===
myisamchk -r --sort-index --analyze *.MYI fixes that number. I'm inclined to believe the myisamchk *.MYI number since the table I'm "optimizing" does get a lot of deletes.
ALTER TABLE [tbl_name] TYPE=innodb
- will OPTIMIZE an INNODB table in the table space as well
In case you programatically only want to optimize if the data_free (deleted) is some percentage of your data length (size) you can find that out via this statement:
show table status
http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html
Also myisamchk -d has similar.
This page does not tell you what type of database privileges you need to execute OPTIMIZE TABLES.
You need SELECT and INSERT permissions to optimize a table.
Don't forget to FLUSH TABLES after execution of any of the following - REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on tables that are mapped into MERGE table.
For InnoDB, if you have your tables in one tablespace, this will make a complete copy of the table within the tablespace, making the tablespace larger by the total table size less the free space you started with. It will not reduce the tablespace size. It can free fragmented space within a table to the tablespace, making that space available to other tables. If you're short of disk space and don't want to enlarge the tablespace you may be able to work around this by altering the table to MyISAM and then back to InnoDB.
Looking for a good to OPTIMIZE / REPAIR specific tables, databases, or servers? Here is a code that may help. Put this code into a PHP file called optimizedb.php
Call on the script like this:
To optimize your server (all its DB's and tables of DB's):
localhost/optimizedb.php?dbname=all
To optimize a database (all its tables):
localhost/optimizedb.php?dbname=mydatabase
To optimize a table only:
localhost/optimizedb.php?dbname=mydatabase.mytable
If you want to use REPAIR (for fixing indexes, and crashed tables), use the same syntax above, but add repair=1 to the end of the URL (optimizedb.php?dbname=all&repair=1)
Enjoy
CODE:
<?
# Please setup your MySQL Config before continuing
# It is found right here at the top of this file...
#########################################################
# MYSQL CONFIG
#########################################################
global $db_user, $db_password, $db, $db_host;
$db_user = "root";
$db_password = "YOURPASSWORD";
$db = "server";
$db_host = "localhost";
# No additional includes are needed to run this script!
# All functions & config are in this file
# CONFIG SHOWN ABOVE
# FUNCTIONS SHOWN AT END OF SCRIPT
#########################################################
$dbname = $_GET["dbname"]; # Try not to edit this, ok? :)
$repair = $_GET["repair"]; # Make this 1 if you want to repair instead
global $DoWhat;
if ($repair==1) {
$DoWhat="REPAIR";
} ELSE {
$DoWhat="OPTIMIZE";
}
#########################################################
# PERMISSIONS required for script to work
#########################################################
# Must have access to MySQL, using the login info above
#
# Must be able to: Show Databases, Show Tables from php
#
# Must be able to: $DoWhat TABLE $db.$table;
#
# Must be able to: Repair Table $db.$table;
#########################################################
#################
# USAGE
#################
# TO OPTIMIZE YOUR ENTIRE SERVER (the $db_host) above
# optimizedb.php?dbname=all
# TO OPTIMIZE A SINGLE DATABASE
# optimizedb.php?dbname=mydatabase
# TO OPTIMIZE A SINGLE TABLE
# optimizedb.php?dbname=mydatabase.mytable
#:)
############################
# ACTUAL SCRIPT
############################
if (strstr($dbname,".")) {
# Optimize Single Table Only
$optresult = SmartQuery("ARR $DoWhat TABLE $dbname;");
$tbname = $optresult[0];
$tbstatus = $optresult[3];
echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
} ELSE {
if ($dbname=="") {
echo "No usage commands provided!<br><br>
optimizedb.php usage commands:<br><br>
# TO OPTIMIZE YOUR ENTIRE SERVER
# optimizedb.php?dbname=all
# TO OPTIMIZE A SINGLE DATABASE
# optimizedb.php?dbname=mydatabase
# TO OPTIMIZE A SINGLE TABLE
# optimizedb.php?dbname=mydatabase.mytable
";
} ELSE {
if ($dbname=="all") {
# Optimize Entire Server
$dbs = SmartQuery("BIGARR show databases;");
echo "<b>Optimizing " . count($dbs) . " databases: DO NOT STOP YOUR SERVER!!!<br>" . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . "<br><br></b>" . ForceEcho() . ForceEcho() . ForceEcho();
for ($y=0; $y < count($dbs); $y++) {
$dbname=$dbs[$y][0];
$optlocations = OptimizeDatabase($dbname);
for ($x=0; $x < count($optlocations); $x++) {
$tbname = $optlocations[$x][0][0];
$tbstatus = $optlocations[$x][0][3];
if ($tbname=="") {
} ELSE {
if ($tbstatus=="") {
} ELSE {
if (strstr($tbstatus,"doesn't exist")) {
} ELSE {
echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>" . ForceEcho() . ForceEcho() . ForceEcho();
}
}
}
}
}
} ELSE {
# Optimize One Database
$optlocations = OptimizeDatabase($dbname);
for ($x=0; $x < count($optlocations); $x++) {
$tbname = $optlocations[$x][0][0];
$tbstatus = $optlocations[$x][0][3];
echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
}
}
}
}
###################################################################
# OPTIMIZE DATABASE FUNCTION
###################################################################
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
###################################################################
# Also shows some usage of the SmartQuery Multi-Dimensional Arrays
###################################################################
function OptimizeDatabase($dbname) {
# Gets all of the tables in $dbname, and optimizes them..
global $DoWhat;
$tables = SmartQuery("BIGARR SHOW TABLES IN $dbname;");
$optresult = '';
for ($x=0; $x < count($tables); $x++) {
$table = $tables[$x][0];
$optresult[$x] = SmartQuery("BIGARR $DoWhat TABLE $dbname.$table;");
}
return $optresult; # returns format like this: $optresult[$x][0][3] thats the status...
}
#################################################
# SMART QUERY - MULTIDIMENSIONAL ARRAY SUPPORT
#################################################
# usage:
# to return a count: $recordcount = SmartQuery("SELECT count(*) from database.table;");
# to return a single info: $record = SmartQuery("SELECT FirstName from database.table WHERE LastName='jones';");
# to return 1 row as an array: $record = SmartQuery("ARR SELECT * from database.table WHERE LastName='jones';");
# $somethingCol1 = $record[0];
# $somethingCol2 = $record[1];
# to return all rows as a multi-dimensional arrays: $record = SmartQuery("BIGARR SELECT * from database.table WHERE LastName='jones';");
# $somethingRow1Col1 = $record[0][0];
# $somethingRow1Col2 = $record[0][1];
# $somethingRow2Col1 = $record[1][0];
# $somethingRow2Col2 = $record[1][1];
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
function SmartQuery ($QueryString) {
global $db, $db_host, $db_user, $db_password;
$con = mysql_connect($db_host,$db_user,$db_password);
if (!$con) {
die("MySQL Database Connection Problem: " . mysql_error() . "\n *");
exit;
}
if (is_array($QueryString)) {
# Array of queries...
for ($x=0; $x < count($QueryString)+1; $x++) {
$arrquery = $QueryString[$x]; # The Query ($x)
if ($arrquery=="") {
# No query
} ELSE {
# has query
if (strtolower(substr($arrquery,0,3))=="arr") {
# Returns a normal ARR of 1 row
$arrquery=trim(substr($arrquery,3));
$returnarray=true;
$returnbig=false;
} ELSE {
if (strtolower(substr($arrquery,0,6))=="bigarr") {
# Returns a big ARR of all rows
$arrquery=trim(substr($arrquery,6));
$returnarray=true;
$returnbig=true;
} ELSE {
$returnarray=false;
$returnbig=false;
}
}
$query = $arrquery;
mysql_select_db("$db");
$mysql_result = mysql_query($query, $con);
if ($returnbig==true) {
# Output large array - special
$ret[0] = mysql_fetch_row($mysql_result); # set first row
for ($y=1; $y < mysql_num_rows($mysql_result); $y++) {
# Multi-dimensional arrays
$ret[$y] = mysql_fetch_row($mysql_result);
}
$buildret[$x]=$ret;
} ELSE {
# Normal output
$ret = mysql_fetch_row($mysql_result);
if ($returnarray==true) {
$buildret[$x] = $ret;
} ELSE {
$buildret[$x]=$ret[0];
}
}
} # for x (array queries
} # check empty
return $buildret;
# END OF ARRAY QUERIES
} ELSE {
# BEGIN OF SINGLE QUERY
# Normal query
if (strtolower(substr($QueryString,0,3))=="arr") {
# Returns a normal ARR of 1 row
$QueryString=trim(substr($QueryString,3));
$returnarray=true;
$returnbig=false;
} ELSE {
if (strtolower(substr($QueryString,0,6))=="bigarr") {
# Returns a big ARR of all rows
$QueryString=trim(substr($QueryString,6));
$returnarray=true;
$returnbig=true;
} ELSE {
$returnarray=false;
$returnbig=false;
}
}
$query = $QueryString;
mysql_select_db("$db");
#error_reporting(0);
$mysql_result = mysql_query($query, $con);
if ($returnbig==true) {
# Output large array - special
$ret[0] = mysql_fetch_row($mysql_result); # set first row
for ($x=1; $x < mysql_num_rows($mysql_result); $x++) {
$ret[$x] = mysql_fetch_row($mysql_result);
}
return $ret;
} ELSE {
# Normal output
$ret = mysql_fetch_row($mysql_result);
if ($returnarray==true) {
return $ret;
} ELSE {
return $ret[0];
}
}
}
}
# Forces the screen to echo some information back, by flooding it with spaces. Do not use this in a loop with 1 million or more, or it could fill your entire screen up to a GB of data.
# use this to force status onto the screen.
# You can edit this :P
# You can edit this :P
# You can edit this :P
# You can edit this :P
function ForceEcho() {
return "
.";
}
?>
the script above is just fine until you find yourself stuck with databases with dashes... you have to add backquotes to database names... script then works almost flawlessly (except it does not check if the database is empty (ie. no tables - it comes up with an error) =)
<?
function OptimizeDatabase($dbname) {
# Gets all of the tables in $dbname, and optimizes them..
global $DoWhat;
$tables = SmartQuery("BIGARR SHOW TABLES FROM `$dbname`;");
$optresult = '';
for ($x=0; $x < count($tables); $x++) {
$table = $tables[$x][0];
$optresult[$x] = SmartQuery("BIGARR $DoWhat TABLE `$dbname`.$table;");
}
return $optresult; # returns format like this: $optresult[$x][0][3] thats the status...
}
?>
The above should work, but instead, lookup on this function for a more professional way of working:
http://www.php.net/mysql_escape_string
Jonny
#################################################
#!/bin/sh
# this shell script finds all the tables for a database and run a command against it
# @date 6/14/2006
# @author Son Nguyen
DBNAME=$2
printUsage() {
echo "Usage: $0"
echo " --optimize <tablename>"
return
}
doAllTables() {
# get the table names
TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
# loop through the tables and optimize them
for TABLENAME in $TABLENAMES
do
mysql -D $DBNAME -e "OPTIMIZE TABLE $TABLENAME;"
done
}
if [ $# -eq 0 ] ; then
printUsage
exit 1
fi
case $1 in
--optimize) doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac
#################################################
Sample Usage:
root@s# ./mysql_optimize.sh --optimize abc
This script will optimize all databases at server based on Son Nguyen comment.
###########
#!/bin/sh
# this shell script finds all the databases and run mysql_optimize_database.sh for them
# @date 15/Fev/2007
# @author Roberto Berto
OPTIMIZE_CMD="mysql_optimize_database"
# get the databases names
DBS=`mysql -e "SHOW DATABASES\G;"| grep 'Database:' | sed -n 's/Database: \(.*\)$/\1/p'`
# loop through the tables and optimize them
for DB in $DBS
do
echo Optimizing $DB ...
$OPTIMIZE_CMD --optimize $DB
done
###########
Add your own comment.