MySQL Storage Engines

(It is essential to understand the features of each storage engine so that you can use them effectively to maximise the performance of your databases.)

Summary: in this tutorial, you will learn various MySQL storage engines. It is essential to understand the features of each storage engine in MySQL so that you can use them effectively to maximize the performance of your databases.

Introduction to MySQL Storage Engines

In MySQL, a storage engine is a software component responsible for managing how data is stored, retrieved, and manipulated within tables. A storage engine also determines the underlying structure and features of the tables.

MySQL supports multiple storage engines, each has its own set of features. To find the available storage engines on your MySQL server, you can use the following query:

SELECT
  engine,
  support
FROM
  information_schema.engines
ORDER BY
  engine;

The query returns 11 storage engines in the engine column and whether it is supported or not in the support column.

If the support column is YES, it means that the corresponding storage engine is supported, or NO otherwise.

If the value in the support column is DEFAULT, which means that the storage engine is supported and used as the default.

Alternatively, you can use the SHOW ENGINES statement to list all available storage engines:

SHOW ENGINES;

To specify a storage engine when creating a new table, you use the ENGINE clause in the CREATE TABLEstatement:

CREATE TABLE table_name(
  column_list
) ENGINE = engine_name;

If you omit the ENGINE clause, MySQL will use the default storage engine for creating the table.

MySQL storage engine features

The following table compares the features of the storage engine in MySQL:

Summary

• A storage engine determines how MySQL stores, retrieves, and manipulates table data.
• MySQL uses InnoDB as the default storage engine.
• Use the ENGINE clause in the CREATE STATEMENT to explicitly instruct MySQL to use a specific storage engine other than the default storage engine.
• Each storage engine has it is own pros and cons, therefore choosing the right storage engine is critical for your application.