Have a mysqldump with Indexes. How do I import it by disabling them?

Question

I am trying to import a large database but because there's an index, it is taking forever to import the database. I tried copying the raw files to a different machine but for some reason, the largest table is not being read giving me an error: can't read file: 'tablename.MYD. So I mysqldumped my old database but it did so with an index. I cannot do it again because it takes a really long time.

I am trying to import the database but am not sure how to do it without indexes. I thought adding an index after import will be much faster. Does anyone have any suggestions?

Solution 1

To disable the indexes and import from a dump file:

alter table tablename disable keys

And then when the import is done:

alter table tablename enable keys

Although copying the file in the manner you mentioned should work. You just have a permission error. On the shell do:

chown mysql.mysql tablename.* and then restart your database. (make sure you are in the folder the table exists at when you use that command) That should fix the issue.

Edit: clarified where you should use the command

Solution 2

By default, mysqldump produces SQL that disables indexes during mass import and applies them afterward. It looks like this:

/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` VALUES (...), (...);
/*!40000 ALTER TABLE `my_table` ENABLE KEYS */;

Comments

[CodeBird]

Hello sorry for the VERY late comment. but I have the same issue as the OP and was searching on SO for a solution before asking. I have a question, would your solution affect the query speed later on or not? thanks.

[Cfreak]

when you enable keys the indexes will be rebuilt. So no the future query speed should not be affected.

[CodeBird]

hmmm so index rebuilding will take time then right? I mean the only difference is that at the place of inserts taking too much time, it will be index rebuilding taking that time or a bit less. sorry too many questions.

[Cfreak]

the reason to disable indexes on import is so mysql doesn't rebuild them after every insert. If you're doing a large number of inserts this would be really slow. Disabling and enabling ensures they only happen once. As @JohnDouthat pointed out in his answer - disabling and enabling is the default (at least with mysqldump)

[CodeBird]

Thanks for the explanation I have 4 tables of 150 million rows, and 11 indexes each, on an AWS RDS can't do anything other than mysqldump.

[pahnin]

Is there anyway to prevent this?

[John Douthat]

To prevent this, call mysqldump with --skip-disable-keys