Example of Storage Engine in MySQL 8

What is a storage engine?

Storage engines in MySQL are responsible for handling a database table's SQL operations, and managing how data is stored and retrieved. Each engine offers different features and advantages, making it essential to select the one that aligns best with your application's needs. Now Let's see the Example of a Storage Engine.

A storage engine is a software module that a database management system uses to create, read, and update data from a database.

There are two types of storage engines in MySQL : transactional and non-transactional.

Example of Storage Engines

InnoDB Storage Engine

It is an engine that performs well and offers many of the required attributes that any database would need. Oracle recommends using InnoDB for tables except for specialized use cases.

Features of InnoDB Engine

1. Full ACID compliance.

2. Commit, rollback, and crash recovery.

3. Row-level locking.

4. FOREIGN KEY referential-integrity constraints.

5. Increase multi-user concurrency (via non-locking reads).

Example of InnoDB Engine

Step 1) Log in to MySQL and check the database status.

Output

mysql> show databases;
+——————–+
| Database
+——————–+
| information_schema
| MySQL
| performance_schema
| sys
| world
+———————+

Step 2) Create a Table In the InnoDB Storage Engine.

Command: Use World;

Output

mysql> use world;
Database changed

Command: CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=InnoDB;

Output

mysql> CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (8.04 sec)

mysql> SELECT * FROM continents;
Empty set (0.00 sec)

Step 3) Insert Data into the Table.

Output

mysql> INSERT INTO continents(id,name)
-> VALUES
-> (1, 'Asia'),
-> (2, 'Africa'),
-> (3, 'Europe'),
-> (4,' North America'),
-> (5,' South America'),
-> (6,' Australia'),
-> (7, 'Antarctica');
Query OK, 7 rows affected (0.82 sec)
Records: 7 Duplicates: 0 Warnings: 0

Step 4) Now get the table information from the Information_schema database.

Output

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS,CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' and TABLE_NAME='continents';
+————+————+——–+————+———————+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | CREATE_TIME |
+————+————+——–+————+———————+
| continents | BASE TABLE | InnoDB | 7 | 2024-09-22 21:05:30 |
+————+————+——–+————+———————+
1 row in set (0.00 sec)

Step 5) Now you can check the data inside the Continents table.

Output

mysql> SELECT * FROM continents;
+—-+—————+
| id | name
+—-+—————+
| 1 | Asia
| 2 | Africa
| 3 | Europe
| 4 | North America
| 5 | South America
| 6 | Australia
| 7 | Antarctica
+—-+—————+
7 rows in set (0.00 sec)

Step 6) Now check whether this engine follows the ACID, Index Creation, and Row Level Locking mechanism.

MEMORY Storage Engine

1. It Creates tables in memory.

2. It is the fastest engine.

3. It provides table-level locking.

4. It does not support transactions.

5. A memory storage engine is ideal for creating temporary tables or quick lookups.

6. The data is lost when the database is restarted.

Example of MEMORY Engine

Step 1) Log in to MySQL and check the database status.

Output

mysql> show databases;
+——————–+
| Database
+——————–+
| information_schema
| MySQL
| performance_schema
| sys
| world
+———————+

Step 2) Drop the continents table and recreate it with the Memory Storage Engine.

Command: Use World;

Output

mysql> use world;
Database changed

mysql> drop table continents;
Query OK, 0 rows affected (0.05 sec)

Command: CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=MEMORY;

Output

mysql> CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM continents;
Empty set (0.00 sec)

Step 3) Insert Data into the Table.

Output

mysql> INSERT INTO continents(id,name)
-> VALUES
-> (1, 'Asia'),
-> (2, 'Africa'),
-> (3, 'Europe'),
-> (4,' North America'),
-> (5,' South America'),
-> (6,' Australia'),
-> (7, 'Antarctica');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

Step 4) Now get the table information from the Information_schema database.

Output

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS,CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MEMORY';
+————+————+——–+————+———————+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | CREATE_TIME |
+————+————+——–+————+———————+
| continents | BASE TABLE | MEMORY | 7 | 2024-09-22 22:44:14 |
+————+————+——–+————+———————+
1 row in set (0.01 sec)

Step 5) Now you can check the data inside the Continents table.

Output

mysql> SELECT * FROM continents;
+—-+—————+
| id | name
+—-+—————+
| 1 | Asia
| 2 | Africa
| 3 | Europe
| 4 | North America
| 5 | South America
| 6 | Australia
| 7 | Antarctica
+—-+—————+

Step 6) Restart MySQL Service and check if the data is there.

Output

Step 7) Now again check the data inside the Continents table.

Note: – The table structure will remain intact, but the data will be absent.

Output

CSV Storage Engine

1. A useful storage engine when data needs to be shared with other applications that use CSV formatted data.

2. The tables are stored as comma-separated value text files.

3. Though this makes sharing the data with scripts and applications easier, one drawback is that the CSV files are not indexed.

4. So, the data should be stored in an InnoDB table until the Import/Export stage of the process.

Example of CSV Engine

Step 1) Log in to MySQL and check the database status.

Output

mysql> show databases;
+——————–+
| Database
+——————–+
| information_schema
| MySQL
| performance_schema
| sys
| world
+———————+

Step 2) Drop the continents table and recreate it with the CSV Storage Engine.

Command: Use World;

Output

mysql> use world;
Database changed

mysql> drop table continents;
Query OK, 0 rows affected (0.05 sec)

Command: CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=CSV;

Output

mysql> CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=CSV;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM continents;
Empty set (0.00 sec)

Step 3) Insert Data into the Table.

Output

mysql> INSERT INTO continents(id,name)
-> VALUES
-> (1, 'Asia'),
-> (2, 'Africa'),
-> (3, 'Europe'),
-> (4,' North America'),
-> (5,' South America'),
-> (6,' Australia'),
-> (7, 'Antarctica');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

Step 4) Now get the table information from the Information_schema database.

Output

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS,CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='CSV' and TABLE_NAME='continents';
+————+————+——–+————+———————+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | CREATE_TIME |
+————+————+——–+————+———————+
| continents | BASE TABLE | CSV | 7 | 2024-09-22 23:19:51 |
+————+————+——–+————+———————+

Step 5) Now you can check the data inside the Continents table.

Output

mysql> SELECT * FROM continents;
+—-+—————+
| id | name
+—-+—————+
| 1 | Asia
| 2 | Africa
| 3 | Europe
| 4 | North America
| 5 | South America
| 6 | Australia
| 7 | Antarctica
+—-+—————+

Step 6) Now you can go to the $DATA_DIR/database_name directory and check if the CSV file is present.

Command to find the DATA_DIR location

mysql> show variables like '%datadir%';
+—————+—————————————+
| Variable_name | Value |
+—————+—————————————+
| datadir | /local/mysqlserver/mysql-8.0.39/data/
+—————+—————————————+
1 row in set (0.01 sec)

We got the data directory path and now we need to go inside the database directory in my case it is WORLD.

BLACKHOLE Storage Engine

1. The Blackhole storage engine accepts but does not store data.

2. Retrievals always return an empty set.

3. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.

4. This storage engine can be used to perform performance tests or other testing.

Example of BLACKHOLE Engine

Step 1) Log in to MySQL and check the database status.

Output

mysql> show databases;
+——————–+
| Database
+——————–+
| information_schema
| MySQL
| performance_schema
| sys
| world
+———————+

Step 2) Drop the continents table and recreate it with the BLACKHOLE Storage Engine.

Command: Use World;

Output

mysql> use world;
Database changed

mysql> drop table continents;
Query OK, 0 rows affected (0.05 sec)

Command: CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=BLACKHOLE;

Output

mysql> CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM continents;
Empty set (0.00 sec)

Step 3) Insert Data into the Table.

Output

mysql> INSERT INTO continents(id,name)
-> VALUES
-> (1, 'Asia'),
-> (2, 'Africa'),
-> (3, 'Europe'),
-> (4,' North America'),
-> (5,' South America'),
-> (6,' Australia'),
-> (7, 'Antarctica');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

Step 4) Now get the table information from the Information_schema database.

Output

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS,CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='BLACKHOLE';
+————+————+———–+————+———————+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | CREATE_TIME |
+————+————+———–+————+———————+
| continents | BASE TABLE | BLACKHOLE | 0 | 2024-09-22 23:41:16 |
+————+————+———–+————+———————+
1 row in set (0.01 sec)

Step 5) Now you can check the data inside the Continents table and guess what it will show as empty.

Output

FEDERATED Storage Engine

1. This storage engine is for creating a single, local, logical database by linking several different physical MySQL servers.

2. No data is stored on the local server.

3. Queries are automatically executed on the respective remote server.

4. It is perfect for distributed data mart environments and can vastly improve performance when using MySQL for analytical reporting.

Note: – This engine is disabled by default So, I have not taken any example for this one, but in case if you want to practice and see the example of this engine as well then please do let me know in the comment section. I would be happy to create and share it with you all.

MyISAM Storage Engine

1. This storage engine supports full-text search indexes.

2. It also supports table-level locking.

3. In this engine there is a lack of support for transactions. (ACID properties)

4. Though it is a fast storage engine, it is best suited for use in read-heavy and mostly read applications such as data warehousing and web applications that don't need transaction support or ACID compliance.

Example of MyISAM Engine

Step 1) Log in to MySQL and check the database status.

Output

mysql> show databases;
+——————–+
| Database
+——————–+
| information_schema
| MySQL
| performance_schema
| sys
| world
+———————+

Step 2) Drop the continents table and recreate it with the MyISAM Storage Engine.

Command: Use World;

Output

mysql> use world;
Database changed

mysql> drop table continents;
Query OK, 0 rows affected (0.05 sec)

Command: CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=MyISAM;

Output

mysql> CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM continents;
Empty set (0.00 sec)

Step 3) Insert Data into the Table.

Output

mysql> INSERT INTO continents(id,name)
-> VALUES
-> (1, 'Asia'),
-> (2, 'Africa'),
-> (3, 'Europe'),
-> (4,' North America'),
-> (5,' South America'),
-> (6,' Australia'),
-> (7, 'Antarctica');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

Step 4) Now get the table information from the Information_schema database.

Output

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS,CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM';
+————+————+——–+————+———————+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | CREATE_TIME |
+————+————+——–+————+———————+
| continents | BASE TABLE | MyISAM | 7 | 2024-09-23 00:01:49 |
+————+————+——–+————+———————+

Step 5) Now check whether this engine follows the ACID mechanism.

ARCHIVE Storage Engine

1. The ARCHIVE storage engine creates specialized tables designed to hold vast amounts of unindexed data, all while maintaining a minimal storage footprint.

2. It Generates .ARZ files that share the same name as the corresponding table.

3. ARZ files are binary data files known as MySQL Archive Storage Engine data files.

4. It uses gzip to compress the rows.

Example of ARCHIVE Engine

Step 1) Log in to MySQL and check the database status.

Output

mysql> show databases;
+——————–+
| Database
+——————–+
| information_schema
| MySQL
| performance_schema
| sys
| world
+———————+

Step 2) Drop the continents table and recreate it with the ARCHIVE Storage Engine.

Command: Use World;

Output

mysql> use world;
Database changed

mysql> drop table continents;
Query OK, 0 rows affected (0.05 sec)

Command: CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=ARCHIVE;

Output

mysql> CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=ARCHIVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM continents;
Empty set (0.00 sec)

Step 3) Insert Data into the Table.

Output

mysql> INSERT INTO continents(id,name)
-> VALUES
-> (1, 'Asia'),
-> (2, 'Africa'),
-> (3, 'Europe'),
-> (4,' North America'),
-> (5,' South America'),
-> (6,' Australia'),
-> (7, 'Antarctica');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

Step 4) Now get the table information from the Information_schema database.

Output

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS,CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='ARCHIVE';
+————+————+———+————+———————+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | CREATE_TIME |
+————+————+———+————+———————+
| continents | BASE TABLE | ARCHIVE | 0 | 2024-09-23 00:22:07 |
+————+————+———+————+———————+

Step 5) Now you can check the data inside the Continents table.

Output

mysql> SELECT * FROM continents;
+—-+—————+
| id | name
+—-+—————+
| 1 | Asia
| 2 | Africa
| 3 | Europe
| 4 | North America
| 5 | South America
| 6 | Australia
| 7 | Antarctica
+—-+—————+

Step 6) Now you can go to the $DATA_DIR/database_name directory and check if the ARZ file is present.

Command to find the DATA_DIR location

mysql> show variables like '%datadir%';
+—————+—————————————+
| Variable_name | Value |
+—————+—————————————+
| datadir | /local/mysqlserver/mysql-8.0.39/data/
+—————+—————————————+
1 row in set (0.01 sec)

We got the data directory path and now we need to go inside the database directory in my case it is WORLD.

FAQ's