MySQL - speed up execution of ALTER TABLE

Question

I have a MySQL DB with innoDB tables. the DB size is about 30GB. I am using ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; to change all the tables characters set and it takes forever!! is there a way to speed up the process?

Answer 1

I suggest you to move the data to temporary table prior to ALTERing it. Should there is any foreign key referencing this table, it should be dropped prior to truncation and recreated after the content restored.

create table tmp_table_name select * from table_name;

/* drop constraints that referencing this table */
-- alter table x drop constraint ...

truncate table table_name;

alter table table_name convert to character set utf8 collate utf8_general_ci;

insert into table_name select * from tmp_table_name;

/* recreate constraints that referencing this table */
-- alter table x add constraint foreign key ...

Hope this would help.

Answer 2

1. create a new table like your source table.
2. alter that new table the way you want.
3. insert your data into the new table.
4. create indexes etc. as needed on the new table.
5. rename your old table to something like ..._old or whatever.
6. rename your new table to the former name of the old one.
7. copy any missing rows from the _old table to the new one.
8. done :)

Answer 3

• This is an undocumented, unsupported technique. Use it at your own risk, and backup your data first. Here are the steps you'll need to take:

1- Create a table of the desired structure, but without any indexes.

2- Load the data into the table to build the .MYD file.

3- Create another empty table with the desired structure, this time including indexes. This will create the .frm and .MYI files you need.

4- Flush the tables with a read lock.

5- Rename the second's table's .frm and MYI files, so mySQL uses them for the first table.

6- Release the read lock.

Answer 4

As some have indicated above, importing the data into a new table rather than trying to alter your existing one makes the most sense to me.

Since you are using innodb, you can disable the doublewrite option temporarily. This has worked for me in the past when restoring data.

This configuration option disables "stores all data twice, first to the doublewrite buffer, and then to the actual data files"

skip-innodb_doublewrite

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite.

Once you've restarted MySQL to update its configuration.

Export the data using mysqldump

mysqldump --single-transaction --default-character-set=utf8 -u user -ppassword mydb nameoftable > /tmp/extracted_table.sql

Then create the new table, load in the data using mysql

mysql --default-character-set=utf8 -u user -ppassword mydb < /tmp/extracted_table.sql

Add your indexes

Remove or comment out the skip-innodb_doublewrite variable and restart mysql.