Why MySQL creates an auto "only one" index for composite key?

Question

In this code:

CREATE TABLE institution (
    iid INT(6) AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    PRIMARY KEY (iid))
ENGINE = INNODB;

CREATE TABLE plan (
    pid INT(2) AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    PRIMARY KEY (pid))
ENGINE = INNODB;

CREATE TABLE subscription (
    iid INT(6),
    pid INT(2),
    PRIMARY KEY (iid, pid),
    CONSTRAINT iid_FOREIGN_KEY FOREIGN KEY (iid) REFERENCES institution (iid),
    CONSTRAINT pid_FOREIGN_KEY FOREIGN KEY (pid) REFERENCES plan (pid))
ENGINE = INNODB;

Why does MySQL only create an auto index for the "pid_FOREIGN_KEY" CONSTRAINT? Why not for "iid_FOREIGN_KEY" too?
If i change "PRIMARY KEY (iid, pid)" to "PRIMARY KEY (pid, iid)" will it create an auto index for "iid_FOREIGN_KEY" and not another one for "pid_FOREIGN_KEY"?

MySQL creates an auto index for "pid_FOREIGN_KEY" CONSTRAINT only

score 3 · Accepted Answer · edited May 23 '17 at 12:01

According to the documentation on FOREIGN KEY Constraints.

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...

MySQL determined that the primary key can support the foreign key reference on the column. If you remove the primary key, both indexes are created implicitly.

The same behavior occurs when using ALTER TABLE on a table that does not have a FOREIGN KEY Constraint.

ALTER TABLE `subscription`
    ADD CONSTRAINT `iid_FOREIGN_KEY` FOREIGN KEY (`iid`) REFERENCES `institution` (`iid`),
    ADD CONSTRAINT `pid_FOREIGN_KEY` FOREIGN KEY (`pid`) REFERENCES `plan` (`pid`);

You can alternatively explicitly define the indexes in your CREATE TABLE statement.

CREATE TABLE `subscription` (
    `iid` INT(6),
    `pid` INT(2),
    PRIMARY KEY (`iid`, `pid`),
    INDEX `iid_FOREIGN_KEY` (`iid`),
    INDEX `pid_FOREIGN_KEY` (`pid`),
    CONSTRAINT FOREIGN KEY (`iid`) REFERENCES `institution` (`iid`),
    CONSTRAINT FOREIGN KEY (`pid`) REFERENCES `plan` (`pid`)
)
ENGINE = INNODB;

Column order impacts the indexing of the values with multiple column indexes, so MySQL determines that the first (left-most) column in the primary key can support the foreign key constraint indexing. see: Multiple Column Indexes.

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

To further clarify, the primary key does have an associated index.

So specifying a single index on the first (left-most) column of the primary key multiple-column index, would be redundant.

Why MySQL creates an auto "only one" index for composite key?

1 Answers1