Storage engines (table types)

MySQL offers several storage engines and more can be added via plugins (storage engines are also madeavailable from third parties and community members). The table below lists some of the table types supported in most MySQL installations. For truly atomic database transactions, you should use InnoDB tables.

Type	Trans- actional	Description	Notes
ARCHIVE	No	This storage engine produces special-purpose tables that store large amounts of unindexed data in a very small footprint	It supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It also has several other limitations. When you create an ARCHIVE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine creates other files, all having names beginning with the table name. The data file has an extension of .ARZ. An .ARN file may appear during optimization operations.
CSV	No	Storage engine that stores data in text files using comma-separated values format	The CSV storage engine does not support indexing and partitioning. When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file.
FEDERATED	No	This storage engine lets you access data from a remote MySQL database without using replication or cluster technology.	When you create a table using one of the standard storage engines (such as MyISAM, CSV or InnoDB), the table consists of the table definition and the associated data. When you create a FEDERATED table, the table definition is the same, but the physical storage of the data is handled on a remote server.
InnoDB	Yes	Transaction-safe tables with row locking (default engine as of MySQL 5.5)	InnoDB is a general-purpose storage engine that balances high reliability and high performance. Advantages include its DML operations following the ACID model, the transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. Assuming innodb_file_per_table option is enabled (def. after 5.6.6 or set in my.cnf), InnoDB stores each table in its own .ibd file. In the same directory the corresponding .frm file is created.
MEMORY	No	Memory-based table; not persistent	The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. The MEMORY storage engine supports both HASH and BTREE indexes.
MERGE	No	A collection of MyISAM tables merged as a single table	Also known as the MRG_MyISAM engine, it is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. An alternative to a MERGE table is a partitioned table, which stores partitions of a single table in separate files. Partitioning enables some operations to be performed more efficiently and is not limited to the MyISAM storage engine.
MyISAM	No	This storage engine is based on the older (and no longer available) ISAM storage engine but has many useful extensions	It is particularly efficient for SELECT queries and has several peculiar characteristics. All data values are stored with the low byte first. This makes the data machine and operating system independent. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

mysql>
  SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='UCAC_2orig';

  SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='UCAC_2orig';

  SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mpe2014' AND table_type != 'View';