MySQL not creating indexes for foreign key

Question

I read that InnoDB automatically creates indexes for Foreign-key.

Does MySQL Workbench automatically create indexes for foreign keys? Does MySQL index foreign key columns automatically? https://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

But some of my foreign keys do not have the index in the table. Check pharmaceutical_id foreign-key field. It does not have an index.

| pharmaceuticals_pharmaceuticalcode | CREATE TABLE `pharmaceuticals_pharmaceuticalcode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code_id` int(11) NOT NULL,
  `pharmaceutical_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pharmaceuticals_pharmaceuticalco_pharmaceutical_id_5ae1e77e_uniq` (`pharmaceutical_id`,`code_id`),
  KEY `pharmaceuticals_phar_code_id_a7de9505_fk_human_api` (`code_id`),
  CONSTRAINT `pharmaceuticals_phar_code_id_a7de9505_fk_human_api` FOREIGN KEY (`code_id`) REFERENCES `human_api_code` (`id`),
  CONSTRAINT `pharmaceuticals_phar_pharmaceutical_id_04c18462_fk_pharmaceu` FOREIGN KEY (`pharmaceutical_id`) REFERENCES `pharmaceuticals_pharmaceutical` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=202770 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

I have added unique-together constraint on pharmaceutical_id and code_id which may caused the not creation of separate index for pharmaceutical_id because MySQL manage these index in B-Tree fashion and index of unique-together key can be used for it.

Check 6th point of restriction and condition on https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 14.6.2.1, “Clustered and Secondary Indexes”).

But If the above point is true then why there is an index for member_id in the below table schema? Because patients_membercard_b5c3e75b index is redundant after patients_membercard_member_id_661ac31abca894ae_uniq

| patients_membercard | CREATE TABLE `patients_membercard` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `patients_membercard_member_id_661ac31abca894ae_uniq` (`member_id`,`name`),
  KEY `patients_membercard_b5c3e75b` (`member_id`),
  CONSTRAINT `patients_member_member_id_459e0d6970a32170_fk_patients_member_id` FOREIGN KEY (`member_id`) REFERENCES `patients_member` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1655520 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

PS: Above table, schema is created by Django and no manual operation has been performed on DB.

Bill Karwin · Accepted Answer · 2019-12-30T09:07:36.493

Your foreign keys do have indexes.

MySQL treats the words KEY and INDEX are synonyms in many contexts.

See What's the difference between using INDEX vs KEY in MySQL?

Re updated question.

I think Django is responsible for defining the extra KEY patients_membercard_b5c3e75b (member_id). It is not created automatically. That is, if I test your CREATE TABLE statement without that KEY definition, the FOREIGN KEY doesn't need it. The FOREIGN KEY can use the left column member_id of the UNIQUE KEY.

I am guessing that the developers who created Django didn't handle this case. They assume that every FOREIGN KEY needs its own index, and they prefer to make sure that they can control the index name. So they generate a definition of an index explicitly in the CREATE TABLE statement.

Yes, key and Index are the same in MySQL. But my problem is either there is a less index in 1st table OR there is an extra index in 2nd table. I have updated the question — SHIVAM JINDAL, Dec 30 '19 at 06:19

MySQL not creating indexes for foreign key

1 Answers1

Linked