MySQL Table Types/Storage Engines

MySQL table types/storage engines are essential features that can be used effectively for maximizing the database's performance. It handles create, read, and update operations for storing and managing the information in a database. In this tutorial, we are going to understand various storage engines or table types used in MySQL.

The following are various table types/storage engines supports in MySQL:

• ISAM
• MyISAM
• MERGE
• InnoDB
• MEMORY (HEAP)
• ARCHIVE
• BDB
• CSV
• FEDERATED

We can use the below query that determines which table types/storage engines our MySQL server supports.

The Support column value indicates whether an engine can be used or not in the current server. A value of YES, NO, or DEFAULT shows that table type is available, not available, and available & currently set as the default table type/storage engine.

It is essential to understand each table type's features in MySQL that enable us to decide the most appropriate among them for our table that maximizes the performance of our databases. Each table type in MySQL has its own advantages and disadvantages. Let us learn each table type/storage engine and its advantages and disadvantages for choosing which one to use among them.

ISAM Table

It is abbreviated as Indexed Sequential Access Method. This table type/storage engine has been deprecated and removed from MySQL version 5.x. MyISAM now replaces the functionalities of this table. The size of an ISAM table is 4 GB, which requires expensive hardware. It is not portable.

MyISAM Table

It is an extension of the ISAM storage engine. The MyISAM table types are optimized for compression and speed and can be easily portable between system to system. Before version 5.5, if we do not specify the table type during table creation, it was the default storage engine. From version 5.x, InnoDB is used as the default table type/storage engine.

The MyISAM table size is dependent on the OS and can be up to 256 TB. It can be compressed into read-only tables that save spaces in memory. MyISAM table type can store 64 keys per table and contains 1024 bytes maximum key length. The MyISAM tables work very fast, but they are not transaction-safe.

Advantages of MyISAM

• If you are new, it will be best to start with MyISAM because it is simple to design and create.
• It is faster than other storage engines in general conditions.
• It provides full-text indexing/searching.

Disadvantages of MyISAM

• MyISAM tables are not transactions-safe.
• It has poor data integrity and crash recovery.
• When we lock the entire table, in that case, MyISAM is slower than InnoDB.

InnoDB Table

The InnoDB tables in MySQL fully support transaction-safe storage engine with ACID-compliant. It is the first table type that supports foreign keys. The InnoDB tables also provide optimal performance. Its size can be up to 64TB. InnoDB tables are also portable between systems to systems similar to MyISAM. InnoDB tables can also be checked and repairs by MySQL whenever necessary.

Advantages of InnoDB

• InnoDB provides optimal performance while processing a large amount of data.
• InnoDB tables arrange our data on the disk based on the primary key.

Disadvantages of InnoDB

• InnoDB tables take more space on disk in comparison with MyISAM.

MERGE Table

MERGE table is also known as MRG_MyISAM. This table combines multiple MyISAM tables with a similar structure (identical column and index information with the same order) into a single table. This table uses indexes of the component tables because it does not have its own indexes. When we join multiple tables, it can also be used to speed up the database's performance. We can perform only INSERT, SELECT, DELETE, and UPDATE operations on the MERGE tables. If we use the DROP TABLE query in this storage engine, MySQL only removed the MERGE specification, and the underlying tables cannot be affected.

Advantages of MERGE

• The main advantage of this table is to remove the size limitation from MyISAM tables.
• It performs more efficient searches and repairs.
• It manages the set of log tables easily.

Disadvantages of MERGE

• MySQL allows us to use only identical (similar structure) MyISAM tables for the MERGE table.
• It cannot support all MyISAM features, such as we cannot create FULLTEXT indexes on MERGE tables.
• It reads indexes slower.

Memory Table

The memory table type/storage engine creates tables, which will be stored in our memory. It is also known as HEAP before MySQL version 4.1. This table type is faster than MyISAM because it uses hash indexes that retrieve results faster. We already know that data stored in memory can be crashed due to power issues or hardware failure. Therefore, we can only use this table as temporary work areas or read-only caches for data pulled from other tables. Hence, the memory/heap tables will be lost whenever the MySQL server halts or restarts. The memory/heap table's data life depends on the uptime of the database server.

Advantages of Memory

The main advantage of this table type is its speed, which is very fast. It is because it uses hash indexing that retrieves results faster.

Disadvantages of Memory

It is not a good idea to use the MEMORY storage for the long term because the data would be lost easily as soon as the power failure or hardware crash.

CSV Table

The CSV table type/storage engine stores data in comma-separated values in a file. It provides a convenient way to migrate data into many different software packages, such as spreadsheet software. This table type is not as good as a general database engine; however, it enables us to exchange our data most effectively and easily. In addition, it will scan the whole table during the read operation.

Advantages of CSV

This table type/storage engine is advantageous when we need to export complex data from one application to a CSV file and then import it into another application.

Disadvantages of CSV

• It is not good to store a large volume of data or larger data types like BLOB, although such types are supported.
• It makes data retrieval slow because there is no indexing.

FEDERATED Table

The FEDERATED table type/storage engine supports MySQL from version 5.03 that allows access data from a remote MySQL server without using the cluster/replication technology. The federated storage engine located in local storage does not store any data. If we will query data from a federated table stored in local memory, MySQL automatically pulled data from the remote federated tables. It is to note that it's a way for a server, not for a client, for accessing a remote database. It is an effective way to combine data from more than one host or copy data from remote databases into local tables without using the data import and export method.

ARCHIVE Table

This table type/storage engine allows us to store a large volume of data in a compressed format to save disk space and cannot be modified. Thus, it is the perfect storage engine to store log data that is no longer in active use, such as the old invoice or sales data. It compresses the data during the insertion and can decompress it using the Zlib library.

The archive tables only support INSERT and SELECT queries. It does not support most of the data types, such as index data type, without which we need to scan a full table for reading rows.

Since it stored the information in a compressed format and if we want to read the table, we first need to decompress the information. This process will take time to perform complex searches and retrievals. Therefore, if we have to perform a large number of queries in these tables, it is beneficial to use another table such as MyISAM.

BDB Table

BDB stands for the Berkeley DB engine, which is developed by SleepyCat software. It is similar to InnoDB in the transaction-safe. It is based on the hash storage mechanism that makes the recovery of information very quickly. It supports page-level locking, but the data file is not portable.