2.6.1.13. Types of storages and their change

MySQL supports two main types of storage:

• MyISAM - has a simpler architecture, tables of this type take up less disk space than InnoDB.
• InnoDB - Supports transactions and foreign keys, but has several significant drawbacks: the space occupied by tables can only increase and never decrease, tables are difficult or impossible to recover in case of failure.

We recommend using MyISAM by default, and using InnoDB only in cases where you need features that are not available in MyISAM (for example, transactions).

There are several ways to change the storage type for individual MySQL tables.

1. Connect to the database with phpMyAdmin or any other MySQLclient.
2. For the tables you want execute SQL query of the form:

   ALTER TABLE table_name ENGINE=InnoDB;

   In the request, instead of table_name specify the desired table, and instead of InnoDB — the type of storage engine you want.

1. Connect to hosting via SSH.
2. To convert all tables to InnoDB run the command:

   for n in `mysql -h host -u login -pPASSWORD name_БД -B -N -e "show tables;"`;do mysql -h host -u login -pPASSWORD name_БД -B -N -e "ALTER TABLE $n ENGINE=innodb;";done

   In the command, specify data to connect to the database (everything except the storage type is specified in two places):

   • host — database server.
   • login — the name of the database user.
   • PASSWORD — database user password.
   • имя_БД — the name of the database.
   • innodb — storage type.

When you run the command, the output might look like this:

mysql: [Warning] Using a password on the command line interface can be insecure.

Do not be intimidated by such a message. This notice only indicates that the use of a password in a command is unsafe. If there is no other data, then the command was executed correctly.

PHP script for converting storage type is presented here.