LINUXMAKER, OpenSource, Tutorials

MyISAM or InnoDB - which machine is the right one?

As well as deciding on Linux against macOS or Windows, or vice versa, this decision depends heavily on which preferences you have. So both storage engines have their advantages and depending on what is needed, MyISAM (My Indexed Sequential Access Method) or InnoDB (Innobase Oy Inc.) is used. Knowing the advantages and disadvantages of both MySQL storage engines gives you a good basis for deciding what to use in each case.

Properties of MyISAM

  • Is used more often and is the standard because there is a high stability.
  • INSERT and UPDATE run faster than InnoDB.
    However, SELECT is slower for that.
  • Integrated full-text search is possible.
  • The write accesses are made only to entire tables.

In the file system, three files are created for each MyISAM table. The names of the files consist of the name of the table and a filename extension that identifies the file type. For example, the table definition stands .frm, for the data is .MYD (MYData) and the index is .MYI (MyIndex).

Initialization of MyISAM

To explicitly create a MyISAM table, the ENGINE option can be specified in SQL.

CREATE TABLE t (i INT) ENGINE = MYISAM;

Properties of InnoDB

  • Referential integrity is automatically maintained.
    However, this must be explicitly determined in advance.
  • SELECT is faster.
    In turn, INSERT and UPDATE are slower for that.
  • The big advantage is the possibility of blocking write accesses to data records.
  • Transactions are supported:
    • Repeated read accesses within a transaction are not affected by write accesses of other transactions (isolation from ACID).
    • The write operations performed in the transaction can be revoked by a transaction abort (rollback).
    • Write accesses in one transaction cause a write lock of affected records for all other transactions (Row level locking).

InnoDB stores the table structure in frm files, payloads and indexes in a table space. The table space is set before starting to work with the database server and can span one or more files. The files in the table space can be distributed to different directories. Similar to a disk partition, the configuration of the table space can not be adjusted retrospectively without risking data loss.

Initialization of InnoDB

To explicitly create a table with InnoDB as the storage subsystem, the SQL option ENGINE can be used.

CREATE TABLE t (i INT) ENGINE = InnoDB;

When will MyISAM be used?

For small databases or applications where much and often data is changed, the use of MyISAM makes sense. MyISAM is also recommended for tables that are read significantly more frequently (SELECT) than written to them (INSERT / UPDATE). Here MyISAM is much more efficient, because INSERT and UPDATE are faster. Above all, a powerful full-text search is supported, which makes sense when texts are to be searched. Due to the lack of support for transactions, inconsistent data can remain in the database in the event of an error, provided that several of the related queries have already been executed, and some other queries have not yet been executed. Likewise, MyISAM lacks referential integrity.
Examples of MyISAM can be found on WordPress or Joomla.

When is INNoDB useful?

InnoDB is especially useful for large databases when many data are linked together. As soon as data is deleted, InnoDB automatically deletes all referenced data. This makes it easier for the application behind the database to maintain referential integrity.
InnoDB is used by Typo3 CMS or MediaWiki.