1

I have a relatively large table (a couple millions of records), multiple websites are querying this table, and from the very beginning the table's engine was MyISAM, currently I'm experiencing difficulties with table locks.

So. i decided to change the Table engine from MyISAM to InnoDB.

The question: are there any hidden stones in that? The DB is live. So i need to be sure that it won't affect any web-sites' functionality.

There are no full-text indexes are currently set up in this table.

rinchik
  • 2,642
  • 8
  • 29
  • 46
  • http://stackoverflow.com/a/4241188/497982 – Nathan Sep 14 '12 at 19:39
  • 1
    Also, I suggest you read [Converting Tables from Other Storage Engines to `InnoDB`](http://dev.mysql.com/doc/en/converting-tables-to-innodb.html). – eggyal Sep 14 '12 at 19:41
  • @Nathan I'm not having a problem changing engines, i just want to make sure that nothing super cereal will happen :o) – rinchik Sep 14 '12 at 19:42

1 Answers1

1

Basically, you'd have to do this:

ALTER TABLE t1 ENGINE=InnoDB;

or this:

INSERT INTO my_innodb_table SELECT * FROM my_myisam_table

Check this: Converting Tables from Other Storage Engines to InnoDB for considerations on the matter, specially:

InnoDB does not have a special optimization for separate index creation the way the MyISAM storage engine does. Therefore, it does not pay to export and import the table and create indexes afterward. 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 ....

Also these: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

Nathan
  • 2,705
  • 23
  • 28