Overview
In this tutorial, we will learn about MySQL Storage Engines. 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
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:
| MySQL Storage Engines | Description |
| InnoDB | ACID compliant transactional storage engine. Supports transactions, row-level locking, and foreign keys. |
| MyISAM | Fast storage engine for ready heavy applications. |
| MEMORY | In memory storage engine. Hash based, stored in memory, useful for temporary tables |
| FEDERATED | Access to database tables located remotely |
| EXAMPLE | Storage engine used only for development and testing |
| BLACKHOLE | Data stored in a table disappears because the engine discards it |
| CSV | Stores data in comma-separated values format as plain text |
| NDB | MySQL’s cluster storage engine |
| ARCHIVE | Archival storage engine. Mostly used for large number of records that will never be altered. |
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)
Display Engine Information
https://www.testingdocs.com/mysql-storage-engine-information/
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: