OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_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 12.4.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 |
status, error,
info, or warning
|
Msg_text |
An informational message |
Note that MySQL locks the table during the time
OPTIMIZE TABLE is running.
By default, OPTIMIZE TABLE
statements are written to the binary log so that they will be
replicated to replication slaves. Logging can be suppressed with
the optional NO_WRITE_TO_BINLOG keyword or
its alias LOCAL.
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.
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.
When using the InnoDB plugin with Barracuda+Compression, issue:
alter table your_table row_format=compressed; to rebuild the table.
Also at MyISAM tables, the optimize needs a whole datafile of free hd space to free the not-used space in the file.
This means, that you need at least (database + data of biggest table) storage at the database directory (my case).
This may be very unfortunate, if you have (some, but) one very big table in your database, which needs almost all the storage... ;(
In this case, it is a very bad idea to optimize this table. Indeed, you can not optimize it, until the data of that big table is less then free space, which is very unlikely (This table must shrink to about eg. 1% its data).
Just tested: mysql will wait for space on the storage, if not enough is available for TABLE OPTIMIZE. Of course, this table will be locked all the time. I got out of this situation only by stopping the mysql server.
The only help I know, is to copy this table to another server with enough space, then optimized it there, and move this optimized table back (which must be done offline, because you have to remove the original first to get the space)
I wrote a quick dos script that seeks out fragmented tables and then runs optimize "in case anyone else can use it thought I would share". Used with MySQL 5.0.68 running on Server 2003.
:: Set Global Variables
set mysqlbin=D:\hyperic\mysql-5.0.68\bin
set mysqlhost=localhost
set mysqlport=3306
set mysqluser=*
set mysqlpw=*
set target_data_free=10
:: sql cmd to get a list of fragmented tables
set get_fragtables=%mysqlbin%\mysql -h%mysqlhost% -P%mysqlport% -u%mysqluser% -p%mysqlpw% --batch -Dinformation_schema --skip-column-names -e "SELECT table_schema, table_name FROM TABLES where table_schema not in ('information_schema','mysql') and data_free > %target_data_free%;"
:: loop all fragmented tables and optimize.
for /f "tokens=1,2* delims= " %%A in ('%get_fragtables%') do (
echo %mysqlbin%\mysql -h%mysqlhost% -P%mysqlport% -u%mysqluser% -p%mysqlpw% --batch -e "optimize table %%A.%%B;"
)
Here you find more information for bulgarian languages- http://mpetrov.net/konstrukcii-na-sql-za-poddryjka-na-tablici-v-mysql . For more info check my tech blog: http://www.mpetrov.net . Freedom of Mysql
Add your own comment.