Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

If you disable the keys (suspending indexing) on a mysql INNODB table, how long does that setting last?

For a query like:

ALTER TABLE users DISABLE KEYS;

Do the keys get re-enabled at the end of the script? or do they last until you explicitly turn indexing back on?

share|improve this question
    
This question deserve a +1 for helping others realize something about DISABLE KEYS, ENABLE KEYS, and InnoDB. –  RolandoMySQLDBA Jul 18 '11 at 16:38

2 Answers 2

The Fine Manual (which is well worth Reading) suggests that the effect of that statement lasts until a corresponding ALTER TABLE ... ENABLE KEYS statement is executed.

share|improve this answer

In reality, ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS do not work !!!!

I addressed this back in February 13, 2011.

I did some additional digging and found out from InnoDB's mother company, InnoBase Oy (before being made 7 of 9 in the Oracle Borg), that this is indeed the case.

This link suggests doing DISABLE KEYS and disabling foreign keys together. Although I think both is unnecessary, this apparently worked for someone.

UPDATE 2011-07-18 12:35 EDT

This is a very good question because it expose an evil that was left over in MySQL. The mysqldump program blindly drops DISABLE KEYS and ENABLE KEYS around the creation and load over every table without regard to storage engine. Since DISABLE KEYS and ENABLE KEYS do not work (in the best case has no effect) on InnoDB tables as it works properly for MyISAM, this unknown fact should be better documented by the MySQL community. Oh yea, MySQL is in the middle of the Galactic Empire known as Oracle. I will not be holding my breath on documentation changes forthcoming.

share|improve this answer
    
Making a statement is one thing. Yelling it out by using both caps and bold is somewhat excessive and completely unnecessary. –  John Gardeniers Jul 18 '11 at 2:58
    
@John : I am sorry I seem overly enthusiastic. I am not angry with anyone. ALTER TABLE DISABLE/ENABLE KEYS against InnoDB is not well documented. Oracle is not going to improve things either. –  RolandoMySQLDBA Jul 18 '11 at 11:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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