Using ALTER for Table Optimization (MySql)

Question

ALTER TABLE [tbl_name] TYPE=innodb

I have just read somewhere that using above alter table statement will optimize an existing table. I am not very sure that this would work and if yes, does it work even if table type is already InnoDB?

Answer 1

InnoDB:

The InnoDB storage engine in MySQL.

• Support for transactions (giving you support for the ACID property).
• Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
• Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
• InnoDB is more resistant to table corruption than MyISAM.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• Another point is that MyISAM is stagnant; all future enhancements will be in InnoDB

InnoDB Limitations:

• No full text indexing (Below-5.6 mysql version)
• Cannot be compressed for fast, read-only

For more info on this:

• http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html
• When to use MyISAM and InnoDB?
• http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html

If your DB is already a innoDB you do not need to make that statement again. As for other suggestions you should use ENGINE instead of TYPE.

ALTER TABLE `table_name` ENGINE = InnoDB;

Answer 2

I am not sure for optimizing existing table but I can corrected your query.

ALTER TABLE `mytable` ENGINE = InnoDB;

Answer 3

Use the ENGINE keyword since TYPE is not supported any more

As of MySQL 5.1.8, TYPE = engine_name is still accepted as a synonym for the ENGINE = engine_name table option but generates a warning. You should note that this option is not available in MySQL 5.1.7, and is removed altogether in MySQL 5.5 and produces a syntax error.

After that your query should work and change the engine for an existing table.