I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need transactions), but the application has been gaining traffic and certain tables have been impacted because of table-locking on updates. That's the reason 2 of the tables are InnoDB now.

The conversion on the smaller tables (100k rows) don't take long at all, causing minimal downtime. However a few of my tracking tables are approaching 50 million rows. Is there a way to speed up an ALTER TABLE...ENGINE InnoDB on large tables? And if not, are there other methods to convert minimizing downtime on these write-heavy tables?

  • 1
    Something to keep in mind: multiple questions in a single post tend to discourage people who can answer one of the questions from posting an answer.
    – BenV
    Jan 6, 2011 at 3:37
  • I VtC as this is rather complicated to answer. You should open as several questions individually.
    – jcolebrand
    Jan 6, 2011 at 4:31
  • i'll gladly take the advice to make it into a single question, but is it recommended to delete this question and just open a new one? the rewrite would mostly be removing the second 2 bullets and altering the first (I updated the title as well to reflect which storage engines) Jan 6, 2011 at 4:49
  • Either would be fine. It's usually easier to write two other questions and delete the one. However, you could just as easily leave this one for "reference" and have the other two refer back to it, as a "this is my overall goal" question.
    – jcolebrand
    Jan 6, 2011 at 4:52
  • edit this one down into one bullet, then post the followup questions. Jan 6, 2011 at 5:19

3 Answers 3


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 (
my_value VARCHAR(40),
date_created DATE,

Here's the path I recommend -

Create a new table object that will replace the old one:

CREATE TABLE my_table_of_love_NEW (
my_value VARCHAR(40),
date_created DATE,

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.


  • 1
    Agreed ... although if it's heavily transactional, you might need to take the database down while doing this. (but an alter table's going to give you a longer period of downtime, most likely)
    – Joe
    Jan 6, 2011 at 23:46
  • Yeah, I figured it would require downtime for the more active tables. I'm going to have to run some tests, but why would ALTER TABLE take longer than INSERT INTO...SELECT on 50 million rows? Jan 7, 2011 at 14:15
  • It won't. Basically MySQL does internally exactly as this poster suggested. It creates a copy of the definition and trickle loads into the copy. Mar 22, 2011 at 15:11
  • I like this method because it skips the "copy to tmp" part, which can take some time for large tables.
    – Haluk
    Jul 28, 2012 at 1:43
  • Let me just add now, that in MySQL 5.7, ALTERs are much faster and easier to cope with.
    – randomx
    Mar 28, 2016 at 21:04

1) Loss protection is a function of paranoia. Always make a backup. If you're really paranoid, make a backup then restore from the backup.

2) This page of the MySQL manual has instructions to convert table types.

The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... ENGINE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

3) PostgreSQL does full-text search, The Sphinx Engine seems to do it for MySQL

  • i will definitely look into sphinx, as I've only recently heard about it. Jan 6, 2011 at 12:57

It's X times easier to optimize whole server (memory configuration, caches, indexes) when you have only one engine used. Mixing myisam with innodb on large databases will always stuck at some point forced by some comprosise for both engine to work good (but not excelent :)

I recommend You to interest in some dedicated full text search engines like sphinx, lucene (solr) and get rid of it from database layer.

Your Answer

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

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