MySQL Forums
Forum List  »  MyISAM

Re: Using DISABLE KEYS on production tables
Posted by: Rick James
Date: September 15, 2014 11:23AM

DISABLE KEYS is likely to be a disaster in a production environment. If a SELECT comes along that needs the disabled index, it will do a full table scan -- this will take a long time, and block writes to the table. This is likely to have a domino effect.

> several hundred thousand rows

Have you tested it? It might not take that long? It will depend on the cache settings.

Plan A: Walk through the PRIMARY KEY of the 'old' table, doing
INSERT IGNORE ...
SELECT ... WHERE pk > $a AND pk <= $a + 1000;
sleep 1 second
This will do the data transfer with only a small impact on the new table. (Yes it will take longer.)
More on walking through a table:
http://mysql.rjweb.org/doc.php/deletebig

Plan B:
INSERT (no IGNORE needed)
SELECT ... new LEFT JOIN old USING (pk) WHERE old.pk IS NULL;
This (or something like it) will find only the new rows -- doing more of the work in the SELECT and less in the INSERT. (No, I don't know if that will help enough to matter.)

Options: ReplyQuote


Subject
Views
Written By
Posted
3586
September 13, 2014 09:54AM
Re: Using DISABLE KEYS on production tables
1683
September 15, 2014 11:23AM


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.