MySQL -- create two indexes, only one appears in SHOW CREATE TABLE output

Question

In have a many-to-many linking table and I'm trying to set up two foreign keys on it. I run these two statements:

ALTER TABLE address_list_memberships
ADD CONSTRAINT fk_address_list_memberships_address_id
FOREIGN KEY index_address_id (address_id)
REFERENCES addresses (id);

ALTER TABLE address_list_memberships
ADD CONSTRAINT fk_address_list_memberships_list_id
FOREIGN KEY index_list_id (list_id)
REFERENCES lists (id);

I would expect that when I run SHOW CREATE TABLE address_list_memberships I'd see this:

[...]
KEY `index_address_id` (`address_id`),
KEY `index_list_id` (`list_id`),
CONSTRAINT `fk_address_list_memberships_list_id` FOREIGN KEY (`list_id`) 
    REFERENCES `lists` (`id`),
CONSTRAINT `fk_address_list_memberships_address_id` FOREIGN KEY (`address_id`) 
    REFERENCES `addresses` (`id`)

But instead I get this:

[...]
KEY `index_list_id` (`list_id`),
CONSTRAINT `fk_address_list_memberships_list_id` FOREIGN KEY (`list_id`) 
    REFERENCES `lists` (`id`),
CONSTRAINT `fk_address_list_memberships_address_id` FOREIGN KEY (`address_id`) 
    REFERENCES `addresses` (`id`)

It looks as though only one index is there. Seems to contradict the MySQL docs which say MySQL automatically creates an index on the referencing column whenever you create a foreign key.

I've noticed this only-one-index thing every time I create two FKs on a table whether I use a GUI tool such as CocoaMySQL or SQLyog, or whether I do it on the command line.

Any illumination of this mystery would be very much appreciated.

Answer 1

I just tried it and it works fine for me. I copied and pasted the ALTER statements you wrote and here is what I get:

mysql> show create table address_list_memberships;

CREATE TABLE `address_list_memberships` (
  `address_id` bigint(20) unsigned NOT NULL,
  `list_id` bigint(20) unsigned NOT NULL,
  KEY `index_address_id` (`address_id`),
  KEY `index_list_id` (`list_id`),
  CONSTRAINT `fk_address_list_memberships_list_id` 
    FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`),
  CONSTRAINT `fk_address_list_memberships_address_id` 
    FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I'm using MySQL 5.0.51a on Mac OS X.

edit: Try the following query to get all the indexes MySQL thinks exist on your table:

SELECT * FROM information_schema.key_column_usage 
WHERE table_schema = 'test' AND table_name = 'address_list_memberships'\G

(I used the 'test' database for my test; you should replace this string with the name of the schema where your table is defined.)

Answer 2

It doesn't really matter. You still have an index on list_id. MySQL requires any foreign key constraint to also have an index on the referencing fields. Since both index_list_id and fk_address_list_memberships_list_id are built on list_id, MySQL probably sees this and uses index_list_id as the index, renaming it to fk_address_list_memberships_list_id. You could even skip declaring the index, since MySQL will do it implicitly in the version you are using.