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 regions.name is indexed, it seems like wrapping this INSERT in DISABLE/ENABLE keys statements would make sense.
Thanks!