CODEWITHSUNDEEP
mysql
Tyrannogyna
Tyrannogyna

Reputation: 621

Why is this composite key not indexed?

I created this table:

CREATE TABLE incident_originator (
    id_incident INT (11) UNSIGNED NOT NULL,
    id_user INT (11) NOT NULL,
    PRIMARY KEY (
        id_incident,
        id_user
    ),
    CONSTRAINT fk_incident_incident_originator FOREIGN KEY (id_incident) REFERENCES incident_table (id_incident) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_user_incident_originator FOREIGN KEY (id_user) REFERENCES users (id_user) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = latin1;

Yet, the fk_user_incident_originator, is indexed, and the fk_incident_incident_originator is not. Why is that? Isn't InnoBD supposed to automatically index all foreign keys? The lack of an index in the id_incident would make joins slower, wouldn't it? The more I read, the less I understand...

Plus, when I add values to the table, they are ordered by the second column and it gets weird to read as a human being.

EDIT: When I do a SHOW INDEX FROM incident_originator; it returns this: 

Non_unique  Key_name    Seq_in_index    Column_name
0   PRIMARY 1   id_incident
0   PRIMARY 2   id_user
1   fk_user_incident_originator 1   id_user

Upvotes: 1

Views: 66

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179314

fk_incident_incident_originator is not

Sure it is.

PRIMARY KEY (id_incident,id_user),

In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

id_incident is the first (meaning left-most) column of the primary key... and the primary key is a perfectly good index for looking up values when enforcing the constraint. To add a second index would be redundant.

The same is true for joins (though joins are not the actual reason foreign keys are always indexed) -- any index that includes all of the joined columns anchored to the left side of the index is quite valid for a join.

they are ordered by the second column and it gets weird to read as a human being.

Tell any humans you know that the database doesn't order its output for the benefit of humans unless the humans use ORDER BY. Result sets without ORDER BY are unordered by definition. The fact that rows are often returned in primary key order is by coincidence, not by design or necessity. This behavior may shift when the table gets larger as the optimizer changes strategies when reading the table... but since the index on id_user is in fact a covering index (it contains all the columns in the table, because all indexes also contain a copy of the primary key... or, more precisely, it contains all the columns needed to satisfy this particular query -- those are sometimes two different things, and this is one of the best reasons not to use SELECT * in your actual code) so the optimizer happens to be selecting it as its source. It reads the rows in index order from whatever index it selects, and that order becomes the entirely coincidental ordering of the result.

Upvotes: 1

Related Questions