Database

MySQL Storage Engines

Posted by author-avatar
0

MySQL Storage Engines

In this tutorial, you will learn about MySQL Storage Engines. MySQL Storage Engines are components that handle how data is stored, handled, and retrieved in MySQL tables. Each storage engine provides different features and performance optimizations, allowing users to choose the one that best suits their needs.

MySQL allows you to choose a storage engine per table. This makes it flexible to optimize tables based on how they are used.

When we create a database table using MySQL, we can choose the storage engine to use. The choice of storage engine made should be according to the best fit of the needs of the application.

Before we can use the storage engine, it should be compiled into the MySQL server and enabled to use. MySQL server uses a modular approach for storage engines. Each storage engine is a software module that is compiled into the server. We can enable storage engines at configuration time or at runtime. Some most common storage engines in MySQL are as follows:

List

MySQL supports many storage engines. MySQL allows us to choose from any of them when creating a table. Each storage engine has specific characteristics and features.

MySQL Storage Engines Description
InnoDB InnoDB is an ACID-compliant transactional storage engine.

  • It is the default engine in MySQL.

  • Supports transactions, row-level locking, and foreign keys.

  • Ensures ACID compliance (Atomicity, Consistency, Isolation, Durability).

  • Good for high reliability and multi-user applications.
MyISAM

  • Older storage engine, now mostly replaced by InnoDB.

  • Fast  storage engine for read-heavy operations.

  • Supports table-level locking only (less concurrency).

  • Does not support transactions or foreign keys.
MEMORY In memory storage engine. Hash based, stored in memory.

  • Stores data in RAM, making it extremely fast.

  • Data is temporary and lost on server restart.

  • Good for temporary tables or quick lookups.
FEDERATED Access to database tables located remotely

  • Allows a table in MySQL to connect to a table in another MySQL server.

  • Useful for distributed databases.

  • Data is not stored locally.
EXAMPLE Storage engine used only for development and testing
BLACKHOLE Data stored in a table disappears because the engine discards it
CSV

  • Stores table data in CSV (Comma Separated Values) format as plain text.

  • Easy to read with external tools like Excel.

  • No indexing or transactions.
NDB MySQL’s cluster storage engine

  • Used in MySQL Cluster environments.

  • Provides high availability and distributed data storage.

  • Supports transactions and replication.
ARCHIVE Archival storage engine. Mostly used for large number of records that will never be altered.

  • Optimized for storing large volumes of historical data.

  • Supports only INSERT and SELECT operations.

  • No indexes (except auto-increment), making it unsuitable for frequent updates.

 

How to Check Available Storage Engines

You can run the following SQL command:

SHOW ENGINES;

This will display a list of available storage engines along with their support status and features.

 

SHOW ENGINES MySQL Command

To specify the storage engine, we can use the ENGINE clause of the CREATE TABLE statement. If we create a table without the explicit ENGINE option, the MySQL database server creates the table using the default storage engine. The default storage engine is defined by the default_storage_engine system variable.

To know the default storage engine on the MySQL server, issue the following command using the MySQL client.

mysql> SHOW VARIABLES LIKE ‘default_storage_engine’;


+————————+——–+
| Variable_name | Value |
+————————+——–+
| default_storage_engine | InnoDB |
+————————+——–+
1 row in set (0.00 sec)

 

Default Storage Engine MySQL

Conclusion

Choosing the right storage engine is crucial based on the use case:

  • Use InnoDB for most applications.

  • Use MEMORY for temporary and fast-access tables.

  • Use ARCHIVE for logging or storing historical records.

Display Engine Information

MySQL Tutorials

MySQL Tutorials on this website:

Newer
JDBC API Components
Older MySQL IDE Tools