Let me start by saying, I hate ALTER. It's evil, IMHO.
Say, this is your current table schema -
CREATE TABLE my_table_of_love (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM CHARSET=utf8;
Here's the path I recommend -
Create a new table object that will replace the old one:
CREATE TABLE my_table_of_love_NEW (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8
Insert all the rows from the old table by name into the new table:
INSERT INTO my_table_of_love_NEW (id,my_value,date_created)
SELECT id,my_value,date_created FROM my_table_of_love;
Smoke test your migration:
SELECT COUNT(*) FROM my_table_of_love_NEW;
SELECT COUNT(*) FROM my_table_of_love;
SELECT a.id,a.my_value,a.date_created FROM my_table_of_love_NEW a
LEFT JOIN my_table_of_love b ON (b.id = a.id)
WHERE a.my_value != b.my_value;
Swap table names so that you can maintain a back up in case you need to rollback.
RENAME TABLE my_table_of_love TO my_table_of_love_OLD;
RENAME TABLE my_table_of_love_NEW TO my_table_of_love;
Proceed to regression testing.
This approach becomes more and more preferable with tables with multiple indexes and millions of rows.
Thoughts?
Best Answer
Is there a maintenance or tuning headache here? ABSOLUTELY !!!
Any benefit to it? I'll pretend I didn't read that.
The MyISAM storage engine always performs a full table lock with each DML (INSERT, UPDATE, DELETE). SELECTs block the daylights out of DML Statements.
Any transactions that mix MyISAM and InnoDB tables will cause even InnoDB tables to degenerate to full table locks because of the presence of even just one MyISAM table. I wrote about that in StackOverflow back in March 2011.
If the only MyISAM table is just the log table you mentioned, that is still very bad. The reason? If 50 DB Connections want to write a single row into the log table, there will 50 full tables locks, 50 inserts. Here is what is worse:
Get the idea ??? This is what awaits you by making the log table MyISAM !!!
If the same log table was InnoDB, 50 DB Connections would perform 50 one line transactions that do not block each other.
Please read these questions for your own benefit on MyISAM and InnoDB
CAVEAT : To be fair, if a MyISAM has a FULLTEXT, then you have no choice but to live with it. MySQL 5.6 will change that problem as InnoDB will support FULLTEXT indexes.