CODEWITHSUNDEEP
mysqloptimization
Mladen B.
Mladen B.

Reputation: 3005

Is there a way to turn off the creation of a temp table during ALTER TABLE?

Is there a way to perform ALTER TABLE in MySQL, telling the server to skip creating a backup of the table first? I have a backup of the table already and I'm doing some tests on it (adding indexes), so I don't care if the table gets corrupted in the process. I'll just restore it from the backup. But what I do care about is for the ALTER TABLE to finish quickly, so I can see the test results.

Given that I have a big MyISAM table (700 GB) it really isn't an option to wait for couple of hours so that MySQL can first finish creating a backup of the original table, before actually adding an index to it.

Upvotes: 0

Views: 95

Answers (1)

Rick James
Rick James

Reputation: 142433

It's not doing a backup; it is building the new version. (The existing table serves as a backup in case of a crash.)

With InnoDB, there are many flavors of ALTER TABLE -- some of which take essentially zero time, regardless of the size of the table. MyISAM (mostly) does the brute force way: Create an empty table with the new schema; copy all the data and build all the indexes; swap tables. For some alters, InnoDB must also do the brute force way: Example changing the PRIMARY KEY.

Upvotes: 1

Related Questions