Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I can set a (MyISAM) table's engine to InnoDB using the query:

alter table tablename engine=InnoDB

which takes some time to complete for large tables. It appears though that if I run the same query again on the same table, it takes some time again (much less but still considerable). I would expect the query to be instantaneous, since the engine is already set to innodb.

My questions are:

  • Is there a single query that conditionally sets the engine, for example

    alter table tablename engine=InnoDB <if not already innodb>

  • Why does the second query have such a delay? (out of curiosity mostly)

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

This will mass convert all MyISAM tables to InnoDB

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN "
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY (data_length+index_length)"
echo "SET SQL_LOG_BIN = 0;" > ConvertMyISAMToInnoDB.sql
mysql -uroot -p -ANe"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql -uroot < ConvertMyISAMToInnoDB.sql

Why does running ALTER TABLE mydb.mytable ENGINE=InnoDB; take long ?

Under the hood it runs something like this:

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;

The delay comes when building a brand new Clustered Index (home of PRIMARY KEY entries)

If you want to totally automate the conversion of just one table, you need this:

SET @mydb = 'mydb';
SET @mytb = 'mytable';
SET @newengine = 'InnoDB';
SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
INTO @ConvertEngineSQL FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
(SELECT table_schema db,table_name tb FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
USING (db,tb)) AA;
SELECT @ConvertEngineSQL;
PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;

Here is a sample from my PC's MySQL setup

mysql> show create table codewaggle.mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
    -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
    -> INTO @ConvertEngineSQL
    -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
    -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
    -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
    -> (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
    -> USING (db,tb)) AA;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ConvertEngineSQL;
+----------------------------------------------+
| @ConvertEngineSQL                            |
+----------------------------------------------+
| ALTER TABLE codewaggle.mytable ENGINE=InnoDB |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (1.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
    -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
    -> INTO @ConvertEngineSQL
    -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
    -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
    -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
    -> (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
    -> USING (db,tb)) AA;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @ConvertEngineSQL;
+-----------------------------------------------------------------------+
| @ConvertEngineSQL                                                     |
+-----------------------------------------------------------------------+
| SELECT 'codewaggle.mytable is Already InnoDB' as "No Need to Convert" |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------------------------+
| No Need to Convert                   |
+--------------------------------------+
| codewaggle.mytable is Already InnoDB |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Give it a Try !!!

share|improve this answer
    
For example, I have a table with 500k entries (nothing fancy). Running the alter table ... engine=innodb takes approx. 47'' to complete. Running it again requires 40''. Shouldn't (or couldn't) mysql simply bypass the query since the table's engine is already innodb? –  periklis Apr 3 '13 at 21:02
    
Updated my answer with a nice SQL script to convert the table or catch on it's already InnoDB. –  RolandoMySQLDBA Apr 3 '13 at 21:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.