Storage Engines

MariaDB supports several different storage engines that are used to handle different table types. The storage engine can be specified in the CREATE TABLE statement using the ENGINE keyword to tell MariaDB how a table should be handled.

The default storage engine for MariaDB is called MyISAM. Using this handler, tables are stored on disk in three files, with extensions .MYI, .MYD, and .frm. An alternative disk-based table handler is InnoDB, which supports some features that are not available in MyISAM such as transactions that you will learn about in Lesson 15. Support for InnoDB must be enabled at compile-time for your MariaDB server.

The MEMORY storage engine is a very fast storage engine that stores tables in memory only. However, because no data is written to disk, the contents of your tables are destroyed when the MariaDB server is stopped. MEMORY is usually used for temporary tables.

The following example creates a new table that uses the InnoDB engine:

CREATE TABLE mytable (
 id INTEGER PRIMARY KEY,
 name TEXT
) ENGINE=InnoDB;

When using a query to create a table, the ENGINE keyword must appear before the query. The following statement uses the MEMORY engine for a temporary table that is created from a query:

CREATE TEMPORARY TABLE presinc_orders ENGINE=MEMORY SELECT * FROM orders WHERE customer_code = 'PRESINC';