Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Per this post, in order to optimize the storage of a MyISAM table, you need to:


It's obvious what OPTIMIZE does for disk space but what does ALTER and ANALYZE do when used after OPTIMIZE? I am asking specifically about disk storage, so for example, I don't care that ANALYZE improves join performance, etc.

share|improve this question
Thank you for catching the typo in my ServerFault post. +1 !!! – RolandoMySQLDBA Jun 17 '13 at 19:33
up vote 1 down vote accepted

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.


OPTIMIZE TABLE tblname; performs the following:


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.

share|improve this answer
Ah, mystery solved. Thanks R.! – RADA Jun 17 '13 at 21:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.