Sunday, 29 June 2014

MySQL dropping index is very slow

I found a good post in server fault on how we can speed up drop index in big table in MySQL. Here is the scripts.
CREATE TABLE patient_new LIKE patient;
ALTER TABLE patient_new DROP INDEX post_text;
ALTER TABLE patient_new DISABLE KEYS;
INSERT INTO patient_new SELECT * FROM patient;
ALTER TABLE patient_new ENABLE KEYS;
ALTER TABLE patient RENAME patient_old;
ALTER TABLE patient_new RENAME patient;
DROP TABLE patient_old;

1 comment: