While working with MySQL databases, you might encounter situations where converting tables from InnoDB to MyISAM becomes necessary. Common scenarios include:

• Migrating legacy systems that rely on MyISAM-specific features
• Optimizing read-heavy workloads where MyISAM performs better
• Reducing memory overhead for temporary tables
• Working with FULLTEXT indexes (pre-MySQL 5.6)

The traditional method requires executing ALTER TABLE statements individually:

ALTER TABLE customers ENGINE=MYISAM;
ALTER TABLE orders ENGINE=MYISAM;
ALTER TABLE products ENGINE=MYISAM;

This becomes tedious when dealing with databases containing dozens or hundreds of tables.

MySQL provides several ways to batch convert storage engines:

Method 1: Using INFORMATION_SCHEMA and Dynamic SQL

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=MYISAM;') 
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
AND engine = 'InnoDB';

This generates all the necessary ALTER statements which you can then execute.

Method 2: Creating a Stored Procedure

For repeated use, create this procedure:

DELIMITER //
CREATE PROCEDURE convert_to_myisam(IN db_name VARCHAR(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = db_name AND engine = 'InnoDB';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ', db_name, '.', tbl_name, ' ENGINE=MYISAM');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- Usage:
CALL convert_to_myisam('your_database');

Method 3: Using mysqldump and Import

For completely recreating the database:

mysqldump -u username -p your_database > dump.sql
sed -i 's/ENGINE=InnoDB/ENGINE=MyISAM/g' dump.sql
mysql -u username -p your_database < dump.sql

Before converting tables, be aware of these implications:

• MyISAM doesn't support transactions (no COMMIT/ROLLBACK)
• No foreign key constraints in MyISAM
• Table-level locking vs. InnoDB's row-level locking
• Different crash recovery mechanisms

Here's a simple benchmark for a read-heavy workload:

-- InnoDB
SELECT * FROM large_table WHERE non_indexed_column LIKE '%term%'; -- 1.87s

-- MyISAM
SELECT * FROM large_table WHERE non_indexed_column LIKE '%term%'; -- 0.92s

However, for write-heavy operations, InnoDB typically performs better.

Consider keeping some tables in InnoDB while converting others:

-- Convert only tables matching a pattern
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=MYISAM;')
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND table_name LIKE 'archive_%'
AND engine = 'InnoDB';

---

When I first needed to convert storage engines in MySQL, I used the basic approach of specifying each table individually:

USE dbname;
ALTER TABLE tablename ENGINE=MYISAM;

This becomes tedious when dealing with databases containing dozens or hundreds of tables. The manual process is error-prone and time-consuming.

Here's a more efficient approach using dynamic SQL generation:

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=MYISAM;') 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' 
AND engine = 'InnoDB';

This query generates all the necessary ALTER TABLE statements which you can then execute. For a completely automated solution:

SET @database = 'your_database_name';
SET @sql = NULL;
SELECT GROUP_CONCAT('ALTER TABLE ', table_name, ' ENGINE=MYISAM;') INTO @sql
FROM information_schema.tables
WHERE table_schema = @database
AND engine = 'InnoDB';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Important considerations when bulk converting:

• Always backup your database before running mass ALTER operations
• For large tables, consider adding ALGORITHM=INPLACE to minimize downtime
• Be aware of feature differences between InnoDB and MyISAM (transactions, foreign keys, etc.)

For GUI users, MySQL Workbench offers schema modification capabilities. Command-line users might prefer:

mysqldump --no-data your_database_name > schema.sql
sed -i 's/ENGINE=InnoDB/ENGINE=MyISAM/g' schema.sql
mysql your_database_name < schema.sql