Let’s stick with the scenario from yesterday:
CREATE TABLE `bla_di_da` (
`bla` int(11) DEFAULT NULL,
`di` int(11) DEFAULT NULL,
`da` int(11) DEFAULT NULL,
KEY `di` (`di`),
KEY `di_2` (`di`),
KEY `di_3` (`di`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Let’s pretend the table contains a lot of rows.
(You could fill it with the row-generator technique, which was used in many questions)
Now, you’d like to get rid of di_2 and di_3. What is the fastest way?
1.: alter table la_di_da drop key di_2, drop key di_3;
2.: alter table la_di_da drop key di_2;
alter table la_di_da drop key di_3;
a) 1 and 2 take the same amount of time
b) 1 ist faster
c) 2 is faster
[just for fun]
b
One would imagine, that dropping an index would take no time at all – like dropping a table.
But most ALTERs actually cause the table to be recreated in a temporary table and then renamed.
1 ALTER is faster than 2 ALTERs, because you get away with recreating the table only once.
Really important, if your table contains a billion rows.
By the way, there was already a question about this ;)