MySQL is very smart. Even it is so smart that I think sometimes I am stupid at all. Yesterday I made a research for our application. We have several huge tables and one of them is very interesting because it has 300 mb data and (wow) 600 mb index space usages. My first thought was to drop several indexes and find a better solution. But when I tried to drop them I got a mystic error:
ERROR 1025 (HY000): Error on rename of ‘/path/to/table1’ to ‘/path/to/table2’.

For your better understanding of my situation I’ve simulated my problem. Let’s say I have two tables user and address. The second table has useridx index on user_id field and foreign key on the same field to user table. It looks very clear and simple.

create table `user` (
 `id` int not null,
 `name` varchar(128),
  primary key (`id`)
) engine=InnoDB;

create table `address` (
 `id` int not null,
 `user_id` int not null,
 `street` varchar(1),
  primary key (`id`),
  index  `useridx` (`user_id`),
  CONSTRAINT `userfk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) engine=InnoDB;

insert into `user` values(1, 'erka');
insert into `address` values(1, 1, 'some street');

If you check indexes on address table you will see all indexes you expect.

mysql> show index from address
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
 | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
 +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
 | address |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
 | address |          1 | useridx  |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
 +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Now let’s play with this table and try to drop useridx index and get my mystic error.

mysql> alter table `address` drop index `useridx`;
ERROR 1025 (HY000): Error on rename of './test/#sql-5815_28'
to './test/address' (errno: 150)

During my research I understood my problem. Let’s recreate address table without useridx index and look at the indexes of this table.

mysql> show index from `address`;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| address |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| address |          1 | userfk   |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Oops… It looks like MySQL created an index for our foreign key automatically. So perhaps MySQL needs an index for InnoDB foreign key but it can reuse already existed one. In this situation you can’t drop that index (useridx in my case) because it is internally used by the foreign key (userfk in my case). Of course MySQL does a great job here and don’t waste a server memory and a server disk space, but you can be disappointed without knowing this behaviour. Now it’s clear that I have to drop userfk foreign key before dropping useridx index.