MySQL supports several storage engines that act as handlers for different table types. Storage engines are sometimes also known as Table Engines. It is the storage engine of a table that manages the table’s SQL operations.
Each engine is designed to address a unique application need, and a database may have different engines for different tables. The two most familiar storage engines are MyISAM and InnoDB. The other storage engines that MySQL supports: Memory, Merge, Archive, Federated, NDB, Blackhole, and Example.
MyISAM offers high performance in read heavy operations. For that reason, it is the most commonly used for websites and data warehousing applications where workload is typically read-only or read-mostly . It has average reliability and provides no transactions support.
InnoDB is the default MySQL storage engine. It is transaction-safe ([tooltip type=”link” link=”#” target=”_self” tooltip=”Atomicity, Consistency, Isolation, Durability” style=”top” ]ACID[/tooltip] compliant) and has commit, rollback, and crash-recovery capabilities to protect user data. It provides row-level locking and Oracle-style consistent nonlocking reads that increase multi-user concurrency and performance. It is suited for write-heavy operations. It arranges your data on disk to optimize queries based on primary keys. InnoDB has been designed for CPU efficiency and maximum performance when processing large data volumes.
It stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports foreign key referential-integrity constraints.
Create a table with a storage engine
When you create a new table, by default, it gets InnoDB storage engine. To specify a storage engine, use ENGINE clause.
CREATE TABLE t1 (int id)
Find out which engines are being used
To find out all the engines your MySQL installation supports, on sql prompt, type:
And to find out storage engine you are using:
SHOW TABLE STATUS;
For a specific table, add a WHERE clause:
SHOW TABLE STATUS WHERE NAME = ‘tablename’;