Summary: In this chapter, we will learn various MySQL table types or storage engines. It is essential to understand the features of each table type in MySQL so that you can use them effectively to maximize the performance of your databases.
Introducing MySQL Table Types or Storage Engines
MySQL supports various table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:
- MyISAM
- InnoDB
- MERGE
- MEMORY (HEAP)
- ARCHIVE
- CSV
- FEDERATED
Each storage engine has its own advantages and disadvantages. In the following sections, we will discuss each storage engine and its features so that you can decide which one to use.
MyISAM
MyISAM extends the former ISAM storage engine. The MyISAM tables are optimized for compression and speed. MyISAM tables are also portable between platforms and operating systems.
MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data files are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.
InnoDB
The InnoDB tables fully support ACID-compliant and transactions. They are also optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. The size of an InnoDB table can be up to 64TB.
Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it takes more disk space.
MERGE
Merge table type is added to treat multiple MyISAM tables as a single table so it removes the size limitation from MyISAM tables.
Using MERGE table, you can speed up performance when joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.
Memory
The Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when there is power failure and sometimes it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
Archive
The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine compresses a record when it is inserted and decompress it using the zlib library as it is read.
The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support indexes, so it is required a full table scanning for reading rows.
CSV
The CSV storage engine stores data in comma-separated values (CSV) file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.
CSV table does not support NULL data type. In addition, the read operation requires a full table scan.
FEDERATED
The FEDERATED storage engine allows you to manage data from a remote MySQL server without using cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pulled automatically from the remote federated tables.
In this tutorial, you have learned various storage engines or table types available in MySQL.
How to Manage Database MySQL Data Types
How to Manage Database MySQL Data Types
