Mysql – How move MySql table to another disk

You should only need to drop and recreate the affected NC index.

Saying that, on a test server, you can see the differences in doing this compared to your strategy of dropping all indexes above.

I reckon dropping/creating the single index would be quicker overall because the data will be shifted around twice otherwise: once char to varchar, another to build the clustered index. Then you have the NC creation overhead.

It depends of your volumetry but you can use a simple INSERT SELECT statement.

You old table:

mysql> desc old_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| fname | varchar(255) | YES  |     | NULL    |       |
| lname | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Imagine you want to replace fname by firstname, lname by lstname and add a birthdate field.

Create your new structure:

CREATE TABLE new_table (
    id int(11), 
    firstname varchar(255), 
    lastname varchar(255), 
    birthdate DATETIME DEFAULT NULL
);

Now, transfert the old data in the new table, note that the new fields (here Birthdate) will be empty:

INSERT INTO new_table (id, firstname, lastname) SELECT id, fname, lname FROM old_table;

It's just a generic example, if you want for specific one, please add your tables structures in your question.

Max.

Edit for question "At what point would it be a good idea to do something else? ": The INSERT SELECT statement locks (write lock) your table to enforce data consitency during operation. I can't answered to the question "at what point" it depends of your server, configs... But imagine your have a table with 1 Million rows, the operation could take some seconds, we'll say 4 seconds for example, so for 4 seconds, your users will not write in your table.