CODEWITHSUNDEEP
mysqlperformanceindexingforeign-keys
Vincent
Vincent

Reputation: 4409

Indexing on foreign keys

Using MySQL. I have 2 basic tables children and groups. Both are straight forward. Children: id, first name, lastname. Groups: id, name. A child can be in 0 to many groups. And a group can contain 0 to many children. This is handled in a third table: childgroups. I have added foreign keys to the children and groups.

CREATE TABLE childgroups 
(
    childId INT,
    groupId INT
);

ALTER TABLE childgroups 
    ADD FOREIGN KEY (fk_child) REFERENCES children(id);
ALTER TABLE childgroups 
    ADD FOREIGN KEY (fk_group) REFERENCES groups(id);

Since a foreign key is not automatically indexed, I want to create an indexes on the childgroups table.

I will be using statements like, on this table:

SELECT childId 
FROM childgroups 
WHERE groupId = 123;

SELECT groupId 
FROM childgroups 
WHERE childId = 1366;

SELECT children.firstname, children.lastname 
FROM children, childgroups, groups 
WHERE groups.name = "Lions";

Is it enough to define just an index on childId and/or groupId?

Or do I also specify a combined index like:

CREATE INDEX childgroups_index ON childgroups (childid);
CREATE INDEX childgroups_index ON childgroups (groupid);
CREATE INDEX childgroups_index ON childgroups (childid, grouppid);

Upvotes: 0

Views: 1516

Answers (1)

Solarflare
Solarflare

Reputation: 11106

Your assumption that foreign keys are not automatically indexed is incorrect, see Using FOREIGN KEY Constraints:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. 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. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

However, your table is missing a unique key on (childId, groupId), otherwise, you would be allowed to add the same combination several times. Thus it would be a good idea to make those two columns the (composite) primary key (which is common for association tables):

CREATE TABLE childgroups 
(
    childId INT,
    groupId INT,
    primary key (childId, groupId)
);

In addition to the implicit index generated by the foreign key on groupId (the foreign key on childId can use the primary key), every index requirement you can think of is met, and you do not need to add any other index on that table.

Upvotes: 1

Related Questions