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.
I tried something similar just now
Here is MySQL for My PC
mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION | 5.5.12-log |
| VERSION_COMPILE_MACHINE | x86 |
| DATADIR | C:\MySQL_5.5.12\data\ |
| VERSION_COMPILE_OS | Win64 |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)
I will run this using MyISAM
- Step 01) create a table called 'rolando'
- Step 02) insert 'dominique' and 'diamond'
- Step 03) copy the table structure to 'pamela'
- Step 04) alter 'pamela' to not have auto_increment
- Step 05) In DOS, copy rolando.MYD to pamela.MYD
- Step 06) run
REPAIR TABLE pamela;
(Rebuild pamela.MYI)
- Step 07) run
SELECT COUNT(1) FROM pamela;
- Step 08) run
SHOW CREATE TABLE pamela\G
- Step 09) run
SELECT * FROM pamela;
- Step 10) insert 'carlik' into pamela
- Step 11) run
SELECT * FROM pamela;
Let's see if these steps are kosher.
Here are Steps 1-4
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rolando
-> (
-> name varchar(20),
-> id int not null auto_increment,
-> primary key (id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from rolando;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
+-----------+----+
2 rows in set (0.00 sec)
mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table rolando\G
*************************** 1. row ***************************
Table: rolando
Create Table: CREATE TABLE `rolando` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql>
Here is Step 6
C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
1 file(s) copied.
C:\>
Here are the rest of the Steps starting at Step 7
mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning | Number of rows changed from 0 to 2 |
| test.pamela | repair | status | OK |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pamela;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
| carlik | 3 |
+-----------+----+
3 rows in set (0.00 sec)
mysql>
Dangerous game, isn't it ???
Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:
The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!
I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?
You got guts, @atxdba !!!
Best Answer
Take a look here at Percona's online schema change tool (anything from Percona is good - I'd advise you to look at all of the tools while you're at it). You could also try Shlomo Noach's online table change tool - test with both and see which one works for you (again, look at his entire toolkit).