patternsqlMinor
Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?
Viewed 0 times
tablesspacetheanalyzediskwhatbenefityoualreadyoptimizing
Problem
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.
OPTIMIZE TABLE ;
ALTER TABLE ENGINE=MyISAM ;
ANALYZE TABLE ;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.
Solution
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:
OK, I just fixed it in ServerFault.
CLARIFICATION
According to the MySQL Documentation on
For MyISAM tables,
The last part on the table's statistics is what
If you do not want
WARNING : This may not be a good idea for a MyISAM that has heavy INSERTs, UPDATEs, and DELETEs because
Please just stick with
As for MyISAM, you need to periodically run one of the following:
- OPTIMIZE TABLE
myisam-tablename;
- ALTER TABLE
myisam-tablenameENGINE=MyISAM; ANALYZE TABLEmyisam-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 TABLEFor 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.Code Snippets
ALTER TABLE ENGINE=MyISAM tblname;
ANALYZE TABLE tblname;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.Context
StackExchange Database Administrators Q#44706, answer score: 3
Revisions (0)
No revisions yet.