MySQL has the ability to specify what storage engine to use on the database level. You would use a certain storage engine for any of the following reasons: amount of data, speed or performance, functionality or data integrity. As well applications compatibility with particular engines.
Since 5.5.5, InnoDB engine is the default engine enabled. Before 5.5.5, it was MyISAM.
The command show engines will display what engines are currently available on your instance:
Can specify the engine when creating the table.
Eg:
CREATE TABLE datatype_mysql (
Datatype_id INT NOT NULL AUTO_INCREMENT,
Datatype VARCHAR(50) NOT NULL,
Type VARCHAR(40) NOT NULL,
PRIMARY KEY ( Datatype_id )
)
ENGINE = MYISAM;
Storage Engines
MyISAM is based on the older ISAM storage engine. Supports Full Text Search Indexes. Data Caches = No. Index Caches = Yes. Uses table-level locking. Good for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate. Only allows one INSERT/UPDATE/DELETE queries
InnoDB has more features than MyISAM. Uses row-level locking that increases performance over table-level locking. Allows parallel INSERT/UPDATE/DELETE queries to be ran on the same table. InnoDB support foreign key functionality.
Merge enables users to have a collection of identical MyISAM tables to be handled by a single table.
Memory (heap) provides in memory tables. Not great for long term usage.
BDB (Berkeley) handles transaction-safe tables and uses a hash based storage system. There are many downfalls to this system including the speed on the un-indexed rows.
Example is a ‘stub’ engine that serves no real purpose except to programmers. Provides the ability to create tables but no information can be inserted or retrieved.
Archive is used for large amounts of data. Works only with SELECT and INSERT statements. Great for logs.
CSV stores the data in plan text files using comma-separated values.
Blackhole doesn’t store any data. Good for testing the database structure, indexes and any queries.
1 Giant Nerd 