Types of MySQL tables and storages engines

 

MySQL storage engines

• A storage engine is a software module (components that handle the SQL operations for different table types.)
• That is used by DBMS uses for create, read, update data from a database.
• Default storage engine is innodb (from version 5.5 and later).
• MySQL uses two types of storage engines:-

1.            Transactional

2.            Non-transactional.

Transactional

• Support ACID properties for commit, rollback, and crash recovery capabilities to protect user data. Ex. INNODB

 Non-transactional.

• No Rollback/Commit is felt. In order to perform the rollback operation, the user will need to do it manually with codes. Ex. MyISAM 

 MySQL supported various storage engines such as:

• InnoDB
• MyISAM
• Memory
• CSV
• Merge
• Archive
• Federated

Example :- The SHOW ENGINES command that the server supports

mysql> SHOW ENGINES\G

 

Output : -

*************************** 1. row ***************************

      Engine: InnoDB

     Support: DEFAULT

     Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

          XA: YES

  Savepoints: YES

*************************** 2. row ***************************

      Engine: CSV

     Support: YES

     Comment: CSV storage engine

Transactions: NO

          XA: NO

  Savepoints: NO

...

The storage engine is specified at the time of the table creation.

Example:-

mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), Cost INTEGER) ENGINE='MyISAM';

 

The ENGINE keyword specifies the storage engine, used in a particular table.

If do not specify the storage engine, then the default storage engine is used. 

 Example:- The storage engine used for a Cars table in mydb database

mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='Cars';

Output:- 

+--------+

| ENGINE |

+--------+

| InnoDB |

+--------+

1 row in set (0,05 sec)

 

Example:- Changes the storage engine in the Cars table from INNODB to MyISAM.

mysql> ALTER TABLE Cars ENGINE='MyISAM';

mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='Cars';

Output:- 

+--------+

| ENGINE |

+--------+

| MyISAM |

+--------+

1 row in set (0,00 sec)

=================================

Types of MySQL tables 

MySQL table types/storage engines are essential features that can be used effectively for maximizing the database's performance.

It handles creation, read, and update operations for storing and managing the information in a database.

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.

1.     mysql> SHOW ENGINES;

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.

ISAM Table

• It is abbreviated as Indexed Sequential Access Method.
• This table type/storage engine has been censored 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 from 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 a 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 repaired 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 in 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 which allows access to 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.
• 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.

HEAP

• These are mostly used for temporary tables because of their incredible speed, but they don’t support a
• lot of the common features of the MyISAM table, such as auto_increment and blob/text columns.
• This type should be used in unique circumstances only. You might use it, for example, if you were working
• with user logs and you wanted to store the information in a temporary table to massage the data, but
• you didn’t necessarily need to keep the data long-term.

 ==============================================

 Choosing a Storage Engine

The various storage engines provided with MySQL are designed with different use cases in mind. The following table provides an overview of some storage engines provided with MySQL, with clarifying notes following the table.

Table:-  Storage Engines Feature Summary

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery (note 1)	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	Yes (note 2)	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes (note 3)	Yes (note 3)	Yes (note 4)	Yes (note 3)	Yes (note 3)
Foreign key support	No	No	Yes	No	Yes (note 5)
Full-text search indexes	Yes	No	Yes (note 6)	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes (note 7)	No	No
Hash indexes	No	Yes	No (note 8)	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support (note 1)	Yes	Limited (note 9)	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics for the data dictionary	Yes	Yes	Yes	Yes	Yes

===============================================