MySQL Forums
Forum List  »  MyISAM

Using DISABLE KEYS on production tables
Posted by: Dave Scofield
Date: September 13, 2014 09:54AM

I'm unclear of the impact DISABLE KEYS has on other connections.

I have a customer-initiated migration process, where their data is migrated from an old database to a new database. So the stored proc contains several INSERT statements that insert into the new db using SELECTed columns from the old db (with some data massaging thrown in). For larger customers, one table migration in particular could be several hundred thousand rows getting inserted into the new db with a single INSERT-SELECT statement.

So, is it safe to use DISABLE KEYS in the stored procedure that does the migration? Since it's an ALTER TABLE command, I would imagine it has to have some sort of impact on other writes happening at the same time?

Also, if I execute DISABLE KEYS, and the INSERT statement blows up the stored procedure somehow, won't that leave indexing disabled on the table?

In case it helps, here's an example of one new table getting populated:

  INSERT IGNORE INTO regions (name)
  SELECT DISTINCT olddb.sales.region FROM olddb.sales
  WHERE olddb.sales.storeid=ImportStoreID;

Since is indexed, it seems like wrapping this INSERT in DISABLE/ENABLE keys statements would make sense.


Options: ReplyQuote

Written By
Using DISABLE KEYS on production tables
September 13, 2014 09:54AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.