MySQL: Innodb or MyISAM — How to Tell?

Storage Engines 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
/ January 9, 2016

Storage Engines

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.

For example:

CREATE TABLE t1 (int id) ENGINE=MYISAM;

Find out which engines are being used

To find out all the engines your MySQL installation supports, on sql prompt, type:

SHOW ENGINES;
InnoDB MyISAM

And to find out storage engine you are using:

SHOW TABLE STATUS; 

InnoDB MyISAM 2

For a specific table, add a WHERE clause:

SHOW TABLE STATUS WHERE NAME = ‘tablename’;