Converting from MyISAM to InnoDB takes a long time

Wow, I didn’t think that with around 80 million rows, MySQL would take 7 hours to convert from MyISAM to InnoDB.

mysql> alter table metaward_achiever ENGINE=INNODB;
Query OK, 76756189 rows affected (6 hours 53 min 57.07 sec)
Records: 76756189  Duplicates: 0  Warnings: 0

mysql> show create table metaward_achiever;
| metaward_achiever | CREATE TABLE `metaward_achiever` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `award_id` int(11) NOT NULL,
  `alias_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `metaward_achiever_award_id` (`award_id`),
  KEY `metaward_achiever_alias_id` (`alias_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77166947 DEFAULT CHARSET=utf8 |
Advertisements
Posted in mysql, sql. 1 Comment »

One Response to “Converting from MyISAM to InnoDB takes a long time”

  1. Blues Says:

    It's most likely because of the multiple indexes you have. Better to dump the table to a file, create a new table and then LOAD TABLE from the file


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: