MYSQL creates index automatically when

Question

I have the following main table:

CREATE TABLE IF NOT EXISTS `table_1` (
  `id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And the following table:

CREATE TABLE IF NOT EXISTS `table_2` (
  `id_1` BIGINT UNSIGNED NOT NULL,
  `id_2` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`id_1`,`id_2`),
  FOREIGN KEY (`id_1`) REFERENCES table_1(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`id_2`) REFERENCES table_2(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For some reason when creating the above tables, I get the following index created automatically by MYSQL:

    Keyname Type    Unique  Packed  Column  Cardinality Collation Null  
      id_2  BTREE     No       No   id_2    94695       A      No   

So MYSQL is creating an index on the second column called id_2 in table_2. Strange enough, it's not created on both foreign keys and if I create only 1 foreign key, MYSQL wouldn't create an index like this.

I tried to drop the index and got the following error:

 Cannot drop index 'id_2': needed in a foreign key constraint

So why does MYSQL need to create an index like this and why it's created on both keys??

Answer 1

Unlike other databases, MySQL creates indexes for foreign key constraints. As explained in the documentation:

index_name represents a foreign key ID. The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key. Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules: . . .

In your case, one of the foreign key declarations is handled by the primary key index, because id_1 is the first key in both of them.