MySQL - how to alter tables from MyISAM into InnoDB?
Posted by Lukas N., Last modified by Lukas N. on 10 February 2016 05:57 AM

For MySQL and MariaDB databases, it is recommended to use the InnoDB engine (or XtraDb for MariaDB). It is NOT recommended to use the MyISAM engine.

InnoDB:

  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Support for transactions (giving you support for the ACID property)
  • 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.
  • 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

The InnoDB storage engine in MySQL.

 

MyISAM:

  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  • Full-text indexing.
  • Especially good for read-intensive (select) tables.

MyISAM Limitations:

  • Table-level locking
  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • Row limit of 4,284,867,296 rows (232)
  • Maximum of 64 indexes per row

The MyISAM storage engine in MySQL.

 

Before alter tables is neccessary to dump databases!

# mysqldump -u root -p  accounts > accounts.sql

Using following SQL query you can check engine type, in this case Accounts database.

MySQL> show table status from accounts;

 Storage engine MyISAM

Now you can see all tables which have to be altered. You can achieve this goal by following SQL commands.

MySQL> ALTER TABLE accounts.aliases ENGINE=InnoDB;
MySQL> ALTER TABLE accounts.domains ENGINE=InnoDB;
MySQL> ALTER TABLE accounts.metadata ENGINE=InnoDB;
MySQL> ALTER TABLE accounts.useraccess engine=InnoDB;
MySQL> ALTER TABLE accounts.users ENGINE=InnoDB;

All tables are now using InnoDB storage engine. You can check it by following SQL query.

MySQL> show table status from accounts;

 Storage engine InnoDB

In case of needed you can change storage engine to InnoDB on other databases such as groupware, anti-spam, directory cache, active sync, webclient cache. Procedure is very similar as on example above.

(97 vote(s))
Helpful
Not helpful

Comments (0)