To some kick ass DBA or someone that works on MySQL internals, this is probably a no brainer.  But, for just joe schmoe power user types, we wonder about these things.  We could not find the answer on the internet, so I decided to blog about it.  We expected the result we found, but I think it's good information.

Have you ever had to make changes to several indexes or columns in a table?  With Phorum, we keep a series of scripts that run queries to upgrade the database as we make changes.  Recently I had to delete about 12 keys and add about 7 back in their place.  I initially thought to make two sql statements.  One to delete indexes and one to create new ones.  But, Thomas, one of the other developers, wondered if that was really any better.  So, I decided to run some tests.

First I altered a table with 70k rows, dropping one key.  That took 16 seconds.  I then added a key to that same table.  Again, about 16 seconds.  So, I then dropped a key and added a key in one query.  Again 16 seconds.  So, I decided to go for the ultimate challenge.  I dropped 12 keys and added back 12 keys all in one query.  TADA!  16 or so seconds.  The table was an InnoDB table, so I repeated after converting the table to MyISAM.  Again, all the alters took about the same time, regardless of the number of changes.

Update

I was told in the comments that the data was not big enough for a real test.  I hold to the fact that because, as Eric Bergen pointed out, the most time is taken in copying the data, the data size is not that important here.  However, I tried it out on a table with 750k rows just to see.  Dropping 4 keys on a table with 12 keys took 1min 46sec.  Adding 4 keys to the same table, (now with 8 keys) took 1min 52sec.  Adding 4 keys and dropping 4 keys in one alter table took 1min 49sec.  So, looks like I was right.  For fun, I decided to drop all the keys except the primary.  That only took 28sec.  That is what I guessed.  The slow part is copying the table to a new table.  That includes filling in indexes.  It is much faster to write a table with indexes than it is a table with 12 indexes.  However, that was not my use case and not the reason for my testing.  But, it is good information to attach to this blog post.