MySQL Database Engines
Database engines provide underlying functionality for MySQL to work with and process data.
MyISAM and InnoDB are the two most typical and well-liked MySQL database engines. The default engine for MySQL is MyISAM (versions earlier than 5.5.5). It depends on your demands and works well in the majority of situations. Another database engine, like InnoDB, may be a superior option in some circumstances. For instance, one of the distinctions between them is that MyISAM does not handle transactions while InnoDB does. In addition, MyISAM does not accept foreign keys, whereas InnoDB does.
Furthermore, you can complete control over how and when MySQL uses the various database engines when you have root access to your server. You can also change the default database engine, change the database engine of a specific table, and more.
Determining The Default Database Engine
Firstly, type the following command at the mysql> prompt:
SHOW ENGINES;
A list of supported engines appears, along with a brief description and the supported features for each engine. The default database engine is marked DEFAULT in the “Support” column.
Changing The Default Database Engine
You can change the default database engine for your MySQL installation. After you do this, all new tables that you create will use the new database engine (unless you explicitly set the engine during table creation).
To change the default database engine, follow these steps:
- Use your preferred text editor to open the my.cnf file on your server. The location of the my.cnf file depends on your Linux distribution:
- On CentOS and Fedora, the my.cnf file is located in the /etc directory.
- On Debian and Ubuntu, the my.cnf file is located in the /etc/mysql directory.
- In the my.cnf file, locate the [mysqld] section.
- Add or modify the following line in the [mysqld] section. Replace ENGINE with the name of the engine that you want to use as the default:
default-storage-engine=ENGINE
If you are enabling the InnoDB database engine, depending on your Linux distribution you may have to disable the following line in the my.cnf file:
skip-innodb
To do this, just add a pound sign (#) to the beginning of the line, as follows:
#skip-innodb
- Save the changes to the my.cnf file, and then exit the text editor.
- Restart the MySQL server using the appropriate command for your Linux distribution:
- For CentOS and Fedora, type:
service mysqld restart
- For Debian and Ubuntu, type:
service mysql restart
- For CentOS and Fedora, type:
- To confirm the new default database engine, use the SHOW ENGINES SQL statement as described in the “Determining the default database engine” section.
Determining A Table's Current Database Engine
To determine which engine a database table is currently using, type the following command at the mysql> prompt. Replace the database with the name of the database that you want to check:
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database';
This command displays a list of every table in the database, along with the engine each table is using.
Changing A Table's Database Engine
You can change the database engine for a table that already exists. For example, the following SQL statement shows how to modify a table named myTable to use the InnoDB engine:
ALTER TABLE myTable ENGINE = InnoDB;
Creating A New Table With A Specific Database Engine
When you create a table in a database, you can explicitly set its database engine (otherwise, MySQL uses the default database engine during table creation). For example, the following SQL statement shows how to create a table named myTable that uses the MyISAM database engine:
CREATE TABLE myTable ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), data VARCHAR(20) NOT NULL ) ENGINE MyISAM;
Similarly, to create a table that uses the InnoDB database engine, you could use the following SQL statement:
CREATE TABLE myTable ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), data VARCHAR(20) NOT NULL ) ENGINE InnoDB;
