Take the 2-minute tour ×
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.

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, and it looks promising.

PS: As I learned, OPTIMIZE TABLE liberates overhead. However, I double checked the overhead column in mysql and it is all empty for my innodb tables ... So, I guess this may not be necessary.

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

MyISAM

Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

This definitely cleans up table fragmentation and computes fresh statistics for indexes

InnoDB

Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

While this eliminates fragmentation, ANAYLZE TABLE is complete useless for InnoDB. I wrote about this a long time ago:

If your data growth rate is very small (or in other words, your dataset stays basically the same size for months at a time), then defragmenting tables would just be overkill. You should probably focus on tuning InnoDB's other aspects (See my Aug 04, 2011 post: Optimizing InnoDB default settings)

I also have posts in the Drupal StackExchange on opimizing MySQL

share|improve this answer
add comment

Your Answer

 
discard

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.