Working with MySQL Database Engines

The key purpose of the database engines provides substantial functionality for MySQL to work with and process data. Database engines are the components of MySQL that can manage the SQL operations for various table types to store and manage information in a database. 

The beginner�s guide to MySQL database engines

What are MySQL database engines?

A storage engine is a software module that a database management system (DBMS) uses to create, read, and update data from a database. For the current version of MySQL the default storage engine is InnoDB.

To learn more about the MySQL database engine, use the show command. The query is as follows ?

mysql> show engines;

The following is the output ?

However, there are two most common and popular MySQL database engines are available i.e. MyISAM and InnoDB. 

For versions earlier than 5.5.5, MyISAM is used as the default MySQL engine for MySQL and functions well in most scenarios. However, it totally depends on your requirements, there are situations where another database engine, such as InnoDB, may be the better option. 

For example, InnoDB supports transactions, on the other hand, MyISAM does not.

Whereas, InnoDB provides support for foreign keys on the contrary MyISAM does not.

Thus, if you have root access to your server, you have complete control over how and when MySQL uses the different database engines. As per your convenience, you can change the default DB engine, a specific table's DB engine, and more.

Let�s dive into the depth.

Determining the default database engine

To confirm the default database engine for your installation, type the following command at the mysql> prompt:

SHOW ENGINES;

On typing this command, a list of supported engines appears, along with a brief description and the supported features for each DB engine. In the support column, the default database engine is marked as DEFAULT.

Changing the default database engine

You can easily change the default database engine for your MySQL installation. Once you do this, all new tables that you create will use the new database engine (unless you explicitly set the engine during table creation).

You need to follow these steps to change the default database engine as follows:

Step 1: Use your preferred text editor to open my.cnf file on your server. The location of my.cnf file depends on your Linux distribution:

• On CentOS and Fedora, the location of the my.cnf file is in the /etc directory.

• On Debian and Ubuntu, the location of the my.cnf file is in the /etc/mysql directory.

Step 2: In the my.cnf file, locate the [mysqld] section.

Step 3: 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

Step 4: Here, you need to save the changes to the my.cnf file, and then exit the text editor.

Step 5: By using the appropriate command for your Linux distribution restart the MySQL server:

For Fedora and CentOS, use command: Copyservice mysqld restart

For Ubuntu and Debian, use command: Copyservice mysql restart

Step 6: To confirm the new default database engine, use the statement SHOW ENGINES SQL 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 database table, you need to explicitly set its DB engine otherwise, during the creation of the table MySQL uses the default DB engine). 

For example, the following SQL syntax shows how to create a table named myTable that uses the MyISAM DB engine:

CREATE TABLE myTable (

       id INT NOT NULL AUTO_INCREMENT,

       PRIMARY KEY (id),

       data VARCHAR(20) NOT NULL

) ENGINE MyISAM;

Likewise, you could use the following MySQL command to create a table that uses the InnoDB database engine:

CREATE TABLE myTable (

       id INT NOT NULL AUTO_INCREMENT,

       PRIMARY KEY (id),

       data VARCHAR(20) NOT NULL

) ENGINE InnoDB;

Was this answer helpful? 8 10