MySQL - Storage Engines

The MySQL Storage Engines

As we already know, a MySQL database is used to store data in the form of rows and columns. The MySQL storage engine is a component that is used to handle the SQL operations performed to manage this data. They work with simple tasks like creating a table, renaming it, updating or deleting it; which is necessary to increase the database performance.

There are two categories of storage engines used: transactional engines and non-transactional engines. Many common storage engines fall into either type of these categories. In MySQL, however, the default storage engine is InnoDB.

Common Storage Engines

Various common storage engines that are used to work with MySQL are as follows −

InnoDB Storage Engine

• ACID Compliant − InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a transactional database engine, ensuring ACID compliance, which means it supports operations like commit and rollback.
• Crash-Recovery − InnoDB offers crash-recovery capabilities to protect user data.
• Row-Level Locking − It supports row-level locking, which enhances multi-user concurrency and performance.
• Referential Integrity − It also enforces FOREIGN KEY referential-integrity constraints.

ISAM Storage Engine

• Deprecated − ISAM, which stands for Indexed Sequential Access Method, was supported by earlier MySQL versions but has been deprecated and removed from recent versions.
• Limited Size − ISAM tables were limited to a size of 4GB.

MyISAM Storage Engine

• Portability − MyISAM is designed for portability, addressing ISAM's non-portable nature.
• Performance − It offers faster performance compared to ISAM and was the default storage engine before MySQL 5.x.
• Memory Efficiency − MyISAM tables have a small memory footprint, making them suitable for read-only or read-mostly workloads.

MERGE Storage Engine

• Logical Combination − MERGE table enables a MySQL developer to logically combine multiple identical MyISAM tables and reference them as one object.
• Limited Operations − Only INSERT, SELECT, DELETE, and UPDATE operations are allowed on MERGE tables. If DROP query is used, only the storage engine specification gets reset while the table remains unchanged.

MEMORY Storage Engine

• In-Memory Storage − MEMORY tables store data entirely in RAM, optimizing access speed for quick lookups.
• Hash Indexes − It uses hash indexes for faster data retrieval.
• Decreasing Use − Its use cases are decreasing; other engines, like InnoDB's buffer pool memory area provide better memory management.

CSV Storage Engine

• CSV Format − CSV tables are text files with comma-separated values, useful for data exchange with scripts and applications.
• No Indexing − They are not indexed, and generally used during data import or export alongside InnoDB tables.

NDBCLUSTER Storage Engine

• Clustering − NDBCLUSTER, also known as NDB, is a clustered database engine suitable for applications that require the highest possible degree of uptime and availability.

ARCHIVE Storage Engine

• Historical Data − ARCHIVE tables are ideal for storing and retrieving large amounts of historical, archived, or secure data. The ARCHIVE storage engines support supports non-indexed tables

BLACKHOLE Storage Engine

• Data Discard − BLACKHOLE tables accept data but do not store it, always returning an empty set.
• Usage − Used in replication configurations, where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.

FEDERATED Storage Engine

• Distributed Databases − FEDERATED allows linking separate MySQL servers to create a logical database from multiple physical servers, useful in distributed environments.

EXAMPLE Storage Engine

• Development Tool − EXAMPLE is a tool in the MySQL source code that serves as an example for developers to start writing new storage engines. You can create tables with this engine, but it doesn't store or retrieve data.

Even though there are so many storage engines that can be used with databases, there is no such thing called a perfect storage engine. In some situations, one storage engine could be a better fit to use whereas in other situations, other engines perform better. Therefore, one must carefully choose what Storage engine to use while working in certain environments.

To choose an engine, you can use the SHOW ENGINES statement.

SHOW ENGINES Statement

The SHOW ENGINES statement in MySQL will list out all the storage engines. It can be taken into consideration while choosing an engine that are supported by the database and are easy to work with.

Syntax

Following is the syntax of the SHOW ENGINES statement −

SHOW ENGINES\G

where, the '\G' delimiter is used to vertically align the result-set obtained from executing this statement.

Example

Let us observe the result-set obtained by executing the SHOW ENGINES statement in a MySQL database using the following query −

SHOW ENGINES\G

Output

Following is the result-set obtained. Here, you can check which storage engines are supported by the MySQL database and where they can be best used −

*************************** 1. row ************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ************************
      Engine: ndbinfo
     Support: NO
     Comment: MySQL Cluster system information storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 11. row ************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
11 rows in set (0.00 sec)

Setting a Storage Engine

Once a storage engine is chosen to be used on a table, you might want to set it while creating the database table. This is done by specifying the type of engine you want to use by adding its name in the CREATE TABLE statement.

If you do not specify the engine type, the default engine (InnoDB for MySQL) will be used automatically.

Syntax

Following is the syntax to set a storage engine in CREATE TABLE statement −

CREATE TABLE table_name (
   column_name1 datatype,
   column_name2 datatype,
   .
   .
   .
) ENGINE = engine_name;

Example

In this example, let us create a new table 'TEST' on MyISAM storage engine using the following query −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = MyISAM;

The result obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

But if we create a table on an engine that is not supported by MySQL, say FEDERATED, an error is raised −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = FEDERATED;

We get the following error −

ERROR 1286 (42000): Unknown storage engine 'FEDERATED'

Changing Default Storage Engine

MySQL also has provisions to change the default storage engine option in three ways −

• Using '--default-storage-engine=name' server startup option.

• Setting 'default-storage-engine' option in 'my.cnf' configuration file.

• Using SET statement

Syntax

Let us see the syntax of using SET statement to change the default storage engine in a database −

SET default_storage_engine = engine_name;

Note − The storage engine for temporary tables, which were created with the CREATE TEMPORARY TABLE statement, can be set separately by setting the 'default_tmp_storage_engine', either at startup or at runtime.

Example

In this example, we are changing the default storage engine to MyISAM using SET statement given as follows −

SET default_storage_engine = MyISAM;

The result obtained is as follows −

Query OK, 0 rows affected (0.00 sec)

Now, let us list the storage engines using SHOW ENGINES statement below. The support column for MyISAM storage engine is changed to default −

SHOW ENGINES\G

Output

Following is the result-set produced. Here, note that we are not displaying the entire result-set and only the MyISAM row for simpler understandability. The actual result-set has 11 total rows −

*************************** 6. row ************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
11 rows in set (0.00 sec)

Altering Storage Engine

You can also alter the existing storage engine of a table to another storage engine using the ALTER TABLE command in MySQL. However, the storage engine must be changed to one that is supported by MySQL only.

Syntax

Following is the basic syntax to change the existing storage engine to another −

ALTER TABLE table_name ENGINE = engine_name;

Example

Consider the previously created table TEST on MyISAM database engine. In this example, using the following ALTER TABLE command, we are changing it to InnoDB engine.

ALTER TABLE TEST ENGINE = InnoDB;

Output

After executing the above query, we get the following output −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the storage engine is changed or not, use the following query −

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'testDB';

The table produced is as shown below −

Storage Engines Using a Client Program

We can also perform storage Engines using the client program.

Syntax

$sql = "SHOW ENGINES";
$mysqli->query($sql);

sql = "SHOW ENGINES";
con.query(sql);

String sql = "SHOW ENGINES";
statement.executeQuery(sql);

sql = "SHOW ENGINES"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SHOW ENGINES";
if($mysqli->query($sql)){
    printf("Show query executed successfully....!\n");
}
printf("Storage engines: \n");
if($result = $mysqli->query($sql)){
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Show query executed successfully....!
Storage engines:
mysqli_result Object
(
    [current_field] => 0
    [field_count] => 6
    [lengths] =>
    [num_rows] => 11
    [type] => 0
)     

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SHOW ENGINES";
 con.query(sql, function(err, result){
    console.log("Show query executed successfully....!");
    console.log("Storage engines: ")
    if (err) throw err;
    console.log(result);
    });
});      

Show query executed successfully....!
Storage engines: 
[
  {
    Engine: 'MEMORY',
    Support: 'YES',
    Comment: 'Hash based, stored in memory, useful for temporary tables',
    Transactions: 'NO',
    XA: 'NO',
    Savepoints: 'NO'
  },
  {
    Engine: 'MRG_MYISAM',
    Support: 'YES',
    Comment: 'Collection of identical MyISAM tables',
    Transactions: 'NO',
    XA: 'NO',
    Savepoints: 'NO'
  },
  {
    Engine: 'CSV',
    Support: 'YES',
    Comment: 'CSV storage engine',
    Transactions: 'NO',
    XA: 'NO',
    Savepoints: 'NO'
  },
  {
    Engine: 'FEDERATED',
    Support: 'NO',
    Comment: 'Federated MySQL storage engine',
    Transactions: null,
    XA: null,
    Savepoints: null
  }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StorageEngine {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SHOW ENGINES";
            rs = st.executeQuery(sql);
            System.out.println("Storage engines: ");
            while(rs.next()) {
              String engines = rs.getNString(1);
              System.out.println(engines);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}     

Storage engines: 
MEMORY
MRG_MYISAM
CSV
FEDERATED
PERFORMANCE_SCHEMA
MyISAM
InnoDB
ndbinfo
BLACKHOLE
ARCHIVE
ndbcluster

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Query to get information about storage engines
storage_engines_query = "SHOW ENGINES"
cursorObj.execute(storage_engines_query)
# Fetching all records about storage engines
all_storage_engines = cursorObj.fetchall()
for row in all_storage_engines:
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()  

('MEMORY', 'YES', 'Hash based, stored in memory, useful for temporary tables', 'NO', 'NO', 'NO')
('MRG_MYISAM', 'YES', 'Collection of identical MyISAM tables', 'NO', 'NO', 'NO')
('CSV', 'YES', 'CSV storage engine', 'NO', 'NO', 'NO')
('FEDERATED', 'NO', 'Federated MySQL storage engine', None, None, None)
('PERFORMANCE_SCHEMA', 'YES', 'Performance Schema', 'NO', 'NO', 'NO')
('MyISAM', 'YES', 'MyISAM storage engine', 'NO', 'NO', 'NO')
('InnoDB', 'DEFAULT', 'Supports transactions, row-level locking, and foreign keys', 'YES', 'YES', 'YES')
('BLACKHOLE', 'YES', '/dev/null storage engine (anything you write to it disappears)', 'NO', 'NO', 'NO')
('ARCHIVE', 'YES', 'Archive storage engine', 'NO', 'NO', 'NO')