When you create a table in MySQL,1) you can choose a storage engine. The storage engine is the way the table data is stored in files.2) There exist several different storage engines, but the most commonly used ones are MyISAM and InnoDB, each of which are the default storage engine in different MySQL versions. If you do not specify a storage engine when creating a table, the default engine for your MySQL version will be used. In MySQL versions earlier than 5.5.5, MyISAM was default, but in versions 5.5.5 and later, InnoDB is the default.3) In most cases, MyISAM is recommended, but there are some cases where for a particular table InnoDB might be recommended instead.
Here is a very brief overview of common types of situations where one of these two engines might be better to use than the other.
If you already have a table created that needs to be changed to a different engine, this can be done with an ALTER TABLE
statement, like this:4)
mysql> ALTER TABLE dbname.tablename ENGINE = enginename;
If you prefer to use phpMyAdmin, you would need to select the table:
click the "Operations" tab:
select the desired engine in the "Storage Engine" dropdown box:
and click the "Go" button:
Congratulations! You have now changed the storage engine of the table.
Here is a little bit of more detailed information about the storage engines, to help explain why different ones are better for different purposes.
.frm
file. This file's name will be the table name followed by this file extension. This file contains table metadata, such as the table definition. Depending on the table's engine5) there may or may not be other files associated with the table.
.frm
file that all tables have, there is also a .MYD
file containing the table data, and a .MYI
file containing the indexes.
innodb_file_per_table
is enabled..frm
file, containing the table definition, as all tables do..ibd
file, containing table data and indexes.innodb_file_per_table
is enabled, recent changes will not yet be written to the table files. This is why databases containing InnoDB tables cannot be individually restored as part of a Partial VPS Restore, which in turn is part of why it is even more important than usual to have automatic backups enabled when InnoDB tables exist.
.frm
file itself, containing the table definition. The table data is stored only in memory.max_heap_table_size
. If the table is filling up, you will need to add more places in the site scripts where the table is emptied.
If a table is composed entirely of ephemeral data that does not need to be kept long, it may be better to store it in MEMORY tables, to combine speed with compartmentalization.
If a table is not often written to,9) especially on a server with many sites, it is likely safest to store it as MyISAM, to better compartmentalize issues in case of the rare instances where InnoDB crash recovery would fail.
If a table is frequently written to and contains non-ephemeral data, then the table should be stored as InnoDB to prevent lock competition from using up too many server resources. If using InnoDB for a table, it is even more important than usual to make sure that cPanel account has automatic backups enabled. If a site is busy enough to need InnoDB tables, it may be time to consider putting the site on its own server, to help prevent an InnoDB failure from affecting other sites.
dbname
with the name of the database, tablename
with the name of the table, and enginename
with either InnoDB
, MyISAM
, or MEMORY
.tmp_table_size
or max_heap_table_size
, whichever is smaller