Storage engines are components of MySQL that manage many SQL operations and Queries.
By Default InnoDB is the most generally used storage engine.
To determine which storage engines are supported by your server, use the SHOW ENGINES statement as shown below.
These are the type of database engines:
- CSV
- MRG_MyISAM
- MEMORY
- Aria
- MyISAM
- SEQUENCE
- InnoDB
- PERFORMANCE_SCHEMA
MariaDB [(none)]> SHOW ENGINES\G;
- CSV: In this storage engine, Data is stored in Comma Separated Value Format in a Text File.
Info from MySQL :
******************************************************
Engine: CSV
Support: YES
Transactions: NO
XA: NO
Savepoints: NO
****************************************************** - MERGE (MRG_MyISAM): This engine combines one or more MyISAM tables to be treated as a single table. It can handle non-transactional tables by default.
Info from MySQL :
******************************************************
Engine: MRG_MyISAM
Support: YES
Transactions: NO
XA: NO
Savepoints: NO
****************************************************** - MEMORY: It provides in-memory tables. It is formerly known as HEAP. This storage engine stores data in RAM, So that the data can be accessed faster, as compared to storing data on disk. This is useful for quick looks up of reference and other identifying data. It is a hash-based system.
Info from MySQL :
******************************************************
Engine: MEMORY
Support: YES
Transactions: NO
XA: NO
Savepoints: NO
****************************************************** - ARIA: Aria is a storage engine for MariaDB and MySQL.It is also known as the relational database management system. The main goal of this system is to make a Crash-Safe system. It is used for internal temporary tables in MariaDB. Aria is not shipped with MySQL.
Info from MySQL :
******************************************************
Engine: Aria
Support: YES
Transactions: NO
XA: NO
Savepoints: NO
****************************************************** - MyISAM: This storage engine, manages non-transactional tables, provides high-speed storage and retrieval, supports full-text searching.It is a Non-transactional engine with good performance and a small data footprint
Info from MySQL :
******************************************************
Engine: MyISAM
Support: YES
Transactions: NO
XA: NO
Savepoints: NO
****************************************************** - SEQUENCE: In this Storage engine records are created in ascending or descending sequence of numbers, with a given starting value, ending value, and increment.
Info from MySQL :
******************************************************
Engine: SEQUENCE
Support: YES
Transactions: YES
XA: NO
Savepoints: YES
****************************************************** - InnoDB: This is the default, mostly used storage engine which is used for MySQL 5.5 or higher. It supports FOREIGN KEY referential-integrity constraints, commit, rollback, and crash recovery capabilities to protect data. Apart from that it also supports row-level locking. It stores data in clustered indexes which reduces I/O for queries based on primary keys so that the Overall performance of this database is very good.
Info from MySQL :
******************************************************
Engine: InnoDB
Support: DEFAULT
Transactions: YES
XA: YES
Savepoints: YES
****************************************************** - PERFORMANCE_SCHEMA: It provides a way to examine the internal execution of the server at runtime. It primarily focuses on performance Data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.
Info from MySQL :
******************************************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Transactions: NO
XA: NO
Savepoints: NO
******************************************************
Get certification for your knowledge in the fundamentals of Computer functioning by clearing the Computer Certification Exam conducted by StudySection. After going through this Computer Certification exam, you will be able to evaluate your basic knowledge of computers.
