Is there a query to set a table's engine to innodb if it's not already? 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 &lt;if not already innodb&gt;
Why does the second query have such a delay? (out of curiosity mostly)
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;" &gt; ConvertMyISAMToInnoDB.sql
mysql -uroot -p -ANe"${SQLSTMT}" &gt; ConvertMyISAMToInnoDB.sql
mysql -uroot &lt; 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&gt; 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&gt; SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

mysql&gt; 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&gt; SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

mysql&gt; 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&gt;


Give it a Try !!!