That was my old ServerFault post from two years ago. Forgive me, it is slightly misworded. It should read:
As for MyISAM, you need to periodically run one of the following:
- OPTIMIZE TABLE
myisam-tablename
;
- ALTER TABLE
myisam-tablename
ENGINE=MyISAM; ANALYZE TABLE myisam-tablename
;
OK, I just fixed it in ServerFault.
CLARIFICATION
OPTIMIZE TABLE tblname;
performs the following:
ALTER TABLE ENGINE=MyISAM tblname;
ANALYZE TABLE tblname;
According to the MySQL Documentation on OPTIMIZE TABLE
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.
The last part on the table's statistics is what ANALYZE TABLE
does.
If you do not want OPTIMIZE TABLE tblname;
to perform ANALYZE TABLE tblname;
, then you must execute ALTER TABLE tblname ENGINE=MyISAM;
instead.
WARNING : This may not be a good idea for a MyISAM that has heavy INSERTs, UPDATEs, and DELETEs because
- This will make table and index statistics stale very quickly
- Stale statistics will cause the Query Optimizer to make bad choices for query EXPLAIN plans
Please just stick with OPTIMIZE TABLE
.
There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all.
All of them creates a unique key/index, constraint md5_constraint unique (query_md5)
has the same effect.
Just pick the name you prefer for the unique key and feel free to use any of your queries. :)
Best Answer
To expand on @MitchWheat's answer (+1 for directly answering first):
ANALYZE TABLE examines key distribution and stores them in INFORMATION_SCHEMA.STATISTICS.
OPTIMIZE TABLE performs ANALYZE TABLE after doing some table compression. The equivalent of
OPTIMIZE TABLE mydb.mytable;
if the table was MyISAM is this:MyISAM
For the MyISAM table mydb.mytable in datadir
/var/lib/mysql
, you have the following files:/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.MYD
(data)/var/lib/mysql/mydb/mytable.MYI
(indexes)OPTIMIZE TABLE mydb.mytable
would shrink the.MYD
and.MYI
files for the table.This is not the same for InnoDB. Here is how it is different:
InnoDB (innodb_file_per_table enabled)
Each table's data and indexes are stored in an external tablespace file. For
datadir
is/var/lib/mysql
and the tablemydb.mytable
, it would be stored as follows:/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd
When
OPTIMIZE TABLE mydb.mytable
is executed,mytable.ibd
gets shrunk.InnoDB (innodb_file_per_table disabled)
Only
/var/lib/mysql/mydb/mytable.frm
would exist. All the data and index pages for the tablemydb.mytable
are stored in the system tablespace file/var/lib/mysql/ibdata1
.When
OPTIMIZE TABLE mydb.mytable
is executed, the data and index pages are written contiguously in ibdata1. Unfortunately, this make ibdata1 grow in leaps and bounds.See the Pictorial Representation from Percona CTO Vadim Tkachenko
UPDATE 2013-02-26 22:33 EST
You comment was
I tried this out
You are correct. You cannot run
OPTIMIZE TABLE
as a single operation. What InnoDB does instead is the following:You could also just run these steps yourself.
However, in all honesty, you should not have to run
ANALYZE TABLE
against an InnoDB table because each time a query is executed, the InnoDB Storage Engine performs an estimation on table cardinality based on passing through pages in the indexes. If there are a high number ofINSERTs
,UPDATEs
, andDELETEs
, then you will need toANALYZE TABLE
. When there is a high number ofDELETEs
, thenALTER TABLE mydb.mytable ENGINE=InnoDB;
is needed to shrink the table.I actually wrote posts about the futility of
ANALYZE TABLE
on InnoDB in certain instances:Oct 16, 2011
: Suddenly have to rebuild indexes to prevent site from going downJun 21, 2011
: From where does the MySQL Query Optimizer read index statistics?