MyISAM

MyISAM is a storage engine employed by MySQL database that was used by default prior to MySQL version 5.5 (released in December, 2009). It is based on ISAM (Indexed Sequential Access Method), an indexing algorithm developed by IBM that allows retrieving information from large sets of data in a fast way.

Pros and Cons

MyISAM is very fast, and provides the best reading speed of all storage engines available in MySQL. However, if your application performs simultaneous reading and writing in one table, the engine’s performance falls down dramatically. This is explained by table-level locking: any time an application inserts data or updates a MyISAM table, all other operations are locked out.

Another big critique of MyISAM is the lack of transactions support. Without transactions, you can’t create referential actions, i.e. operations modifying several linked tables at once. For example, say that you have a CUSTOMERS table containing the list of customers, and an ORDERS table containing the list of orders (each order is associated with some customer). The idea of transactions implies that deleting a row in the “parent” table (CUSTOMERS) should delete all rows of the “child” table (ORDERS) that have a matching foreign key, and this operation can’t be stopped in the middle. Transaction-safe storage engines automatically roll back all operations included in a transaction if some of them fail to complete.

Handy Backup supports “live” backup and recovery of MyISAM tables that can be done in real time and doesn’t expect you to stop your MySQL server. To learn more, please see the MySQL Backup feature page.


Recommended Solution

Handy Backup Small Server

Version 7.19.0, built on 8 November, 2018. 164 MB
Backup Software from Novosoft LLC. 249 USD per license.

Handy Backup Small Server

To back up MySQL database with MyISAM-based tables, you need the Small Server edition of our software. Small Server is one of the best backup solutions for small business awarded by PC Mag in 2012.


MyISAM and InnoDB

Since MySQL 5.5, default storage engine changed to InnoDB. This doesn’t mean that InnoDB is better than MyISAM: as MySQL developers say, this was done because most users never change default settings, and InnoDB is more of general purpose engine.

At Handy Backup testing lab, we have done a simple comparative test. When there were no applications using MySQL, MyISAM was almost ten times faster than InnoDB:

Storage engine

INSERT row time, microseconds

UPDATE row time, microseconds

DELETE row time, microseconds

MySQL MyISAM

150.3

145.57

147.52

MySQL InnoDB

1494.43

1682.67

1892.46

Comparison of MyISAM and InnoDB speed on an idle database with an isolated query

However, when we put the database under load and the tables started receiving updates, InnoDB showed no significant reduction of speed, but MyISAM slowed down to 0.5 seconds and more.

Creating MyISAM Tables in MySQL 5.5 and Higher

In the last versions of MySQL, to create a table with MyISAM as a storage engine you need to add ENGINE=MyISAM to your CREATE TABLE statements, e.g:

CREATE TABLE table1 (I INT) ENGINE=MyISAM;

Or you can change the default engine back to MyISAM by issuing SET default_storage_engine=MyISAM.

Video Example

In the following video tutorial you can learn how to back up and restore MySQL databases with Handy Backup.

Note: Instructions in this video suggest that you already have Handy Backup installed on your computer. If you still haven’t done so, don’t hesitate to download it.

Target Uses of MyISAM

At last, let’s discuss main target uses for the MyISAM storage engine:

  • Read-only tables. If your applications use tables that are never or rarely modified, you can safely change their storage engine to MyISAM.
  • Replication configuration. Replication enables you to automatically keep several databases synchronized. Unlike clustering, in which all nodes are self-sufficient, replication suggests that you assign different roles to different servers. Particularly, you can make an InnoDB-based Master database which is used for writing and processing data and MyISAM-based Slave database which is used for reading. To learn more, please read MySQL Replication.
  • Backup. The most effective approach to MySQL backup is a combination of Master-to-Slave replication and backup of Slave Servers. This is where Handy Backup proves its worth!

Version 7.19.0, built on 8 November, 2018
164 MB

See also:

See all terms from Databases...

Backup Terms Glossary

Contact Us

support@handybackup.net

Term of the Day

  • Glossary Category: Storage Media
    “The “Storage Media” category contains descriptions of different physical media and devices which you can use to store data on.”

Backup Software Glossary

Current glossary explains various terms that you may come across while working with Handy Backup or other backup software.

Handy Backup is a backup utility for Windows Servers and PCs that is famous for its flexibility and ease of use.

The glossary contains 179 terms. The current section Databases contains 18 terms.

Other “Databases” Terms

  • Database Replication
    “Database replication is a process of synchronization in which data added to one database is automatically copied to the other. Replication should not...”
  • Database Dump
    “Database dump is a text file containing a set of SQL statements that need to be run on an SQL server to create a database with the entire internal...”
  • MySQL Login Scripts
    “ MySQL login scripts are vital part of software using MySQL such as websites and forums with dynamic content, business apps based on MySQL...”

Who uses our backup software?