Database storage engines and types

Let's have a look at different storage engine of MySQL database. This is an important section to understand before we jump into data query techniques, as storage engines play an important role in data query techniques. MySQL stores data in the database as a subdirectory. In each database, data is stored as tables and each table definition information is being stored in a file with extension as .frm with the same name as the table name. Suppose if we create a new table as admin_user then it will store all table definition related information in admin_user.frm file.

We can see information related to a table with the use of SHOW TABLE STATUS command. Let's try to execute this command for admin_user table and pull the information.

mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ***************************
 Name: admin_user
 Engine: InnoDB
 Version: 10
 Row_format: Dynamic
 Rows: 2
 Avg_row_length: 8192
 Data_length: 16384
 Max_data_length: 0
 Index_length: 16384
 Data_free: 0
 Auto_increment: 3
 Create_time: 2017-06-19 14:46:49
 Update_time: 2017-06-19 15:15:08
 Check_time: NULL
 Collation: utf8_general_ci
 Checksum: NULL
 Create_options:
 Comment: Admin User Table
1 row in set (0.00 sec)

This command shows that this is an InnoDB table with the column name Engine. There is additional information that you can refer for other purposes like the number of rows, index length, and so on.

The storage engine helps to handle different SQL operations for the various table types. Each storage engine has its own advantages and disadvantages. The choice of storage engine would always depend on the needs. It is important to understand features of each storage engine and choose the most appropriate one for your tables to maximize the performance of the database. In MySQL 8, whenever we create a new table then default storage engine is set as InnoDB.

We can say plug and play kind of storage engine architecture used by MySQL Server because we can easily load and unload the storage engines from the MySQL Server. We can see all supported storage engines with the help of SHOW ENGINES command. This will provide enough information as the storage engine is supported by MySQL Server or not, what is the default storage engine used by MySQL Server. Let's execute this command and pull that information.

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