1

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)

2 Answers 2

1

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 !!!

2
  • 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, 2013 at 21:02
  • Updated my answer with a nice SQL script to convert the table or catch on it's already InnoDB. Apr 3, 2013 at 21:16
0

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 14.12.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

Source

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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