MySQL MyISAM Storage Engine

Summary: in this tutorial, you will learn about MySQL MyISAM storage engine and its features.

Introduction to MySQL MyISAM storage engine

MyISAM is a storage engine based on a deprecated ISAM (Indexed Sequential Access Method) storage engine.

MyISAM is a lightweight, non-transactional engine with high performance. It is also easy to copy between systems and has a very small data footprint.

MyISAM was the default storage engine in MySQL until version 5.5 when MySQL changed it to InnoDB. MySQL has also incorporated the major features of MyISAM into InnoDB, rendering MyISAM somewhat obsolete.

To create a MyISAM table, you use the set the ENGINE clause of the CREATE TABLE statement to MyISAM:

CREATE TABLE table_name(
   ... 
) ENGINE=MYISAM;Code language: SQL (Structured Query Language) (sql)

When creating a MyISAM table, the storage engine generates two files with the same name as the table, each having the following extensions:

.MYD – MyData is the data file.
.MYI – MyIndex is the index file.

The storage engine stores the table definition in the MySQL data dictionary.

MyISAM storage engine does not support transactions, foreign keys, and row locking. Also, the storage limit of a MyISAM table is 256TB.

MyISAM storage engine does support full-text search indexes, geospatial data type, and indexing.

Converting MyISAM to InnoDB

If you want to convert all of your tables from MyISAM to InnoDB, you can follow these steps:

First, list all MyISAM tables of a specific database:

SELECT 
  table_name 
FROM 
  information_schema.tables 
WHERE 
  table_schema = 'database_name' 
  AND engine = 'MyISAM';Code language: SQL (Structured Query Language) (sql)

Second, convert each table to InnoDB using the ALTER TABLE statement:

ALTER TABLE table_name ENGINE = InnoDB;Code language: SQL (Structured Query Language) (sql)

If you need to convert all MyISAM tables in a database to InnoDB, you can use the following stored procedure:

DELIMITER //
CREATE PROCEDURE ConvertMyISAMToInnoDB(IN dbName VARCHAR(100))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(100);
  DECLARE cur CURSOR FOR
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = dbName AND engine = 'MyISAM';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO tableName;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @alterSql = CONCAT('ALTER TABLE `', dbName, '`.`', tableName, '` ENGINE=InnoDB;');
    PREPARE stmt FROM @alterSql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

The stored procedure ConvertMyISAMToInnoDB accepts a database name (dbName) as a parameter and does the following

First, use a cursor to iterate through the list of tables in the specified database (dbName) where the engine type is MyISAM
Second, update each table to InnoDB using dynamic SQL.

For example, to convert all MyISAM tables in the HR database to InnoDB tables, you call the stored procedure ConvertMyISAMToInnoDB like this:

CALL ConvertMyISAMToInnoDB('HR');Code language: JavaScript (javascript)

Summary

Use the MyISAM storage engine for read-heavy workloads and applications that require full-text search capabilities.
MyISAM storage engine doesn’t support some important features such as transactions and foreign keys, which are essential for data integrity in many applications.

Was this tutorial helpful?