How to Manipulate MySQL Storage Engines?

MySQL DBMS offers a wide range of storage engines which are basically software modules used to perform actions like CREATE, INSERT, READ and DELETE in DBMS. Depending upon the user’s requirement, we can access various storage engines for MySQL DBMS. For MySQL 5.5 and later versions, the default storage engine is InnoDB.It is necessary to choose the right engine if we are creating a production database and it will also impact future development.

To find the list of MySQL Storage engines, we use the following syntax:

Syntax :

<pre>SHOW ENGINES;</pre>

Query:

SHOW ENGINES;

Output:

CSV,MyISAM,InnoDB,SEQUENCE etc listed above are the MySQL storage engines.

Now to Set a Particular Storage Engine for a Database we use follow syntax:

Syntax :

<pre>ENGINE = "engineName"</pre>

In this example, we are creating a table named ‘information’ with a column named ‘ID‘.As mentioned before, InnoDB is the default storage engine. Thus, we will change the storage engine to MyISAM.

Query:

CREATE TABLE gfg(
Id INT PRIMARY KEY, 
) ENGINE='MyISAM';

Output :

As shown in the gif, we have set the storage engine to MyISAM.Now, let’s verify it.

Syntax:

Syntax to access the storage engine
<pre>SELECT ENGINE FROM 'DataBaseName'.TABLES</pre> 

Code:

SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='info' 
//info is the database name
AND TABLE_NAME='information'; 
//information is the table name

Output:

As seen in the gif, the storage engine is set to ‘MyISAM’.

Step to Change the Storage Engine of the Already Created MySQL Table:

There are two methods to change the storage engine:

• Using MySQL CLI
• Using MySQL WorkBench                                                                                                                                                                                       

Syntax:

Syntax to change the storage engine
<pre>ALTER TABLE table_name ENGINE engine_name;</pre>

We will use the same table created above to change the storage engine.

Code:

ALTER TABLE information ENGINE = 'InnoDB';  

Output:

We have thus successfully changed the storage engine to the default engine “InnoDB”.

In this article, we have learned to manipulate the storage engine in MySQL for better performance.