MySQL table foreign key indexes on primary index columns

Question

I've created a table for accounts/users with a primary key (UsersID, AccountsID) like below. Should I add the index for the Users table?

create table AccountsUsers
(
    AccountsID int unsigned not null,
    UsersID int unsigned not null,
    Roles bigint unsigned null,
    primary key (UsersID, AccountsID),
    constraint AccountsUsers_Accounts_ID_fk
        foreign key (AccountsID) references Accounts (ID)
            on update cascade on delete cascade,
    constraint AccountsUsers_Users_ID_fk
        foreign key (UsersID) references Users (ID)
            on update cascade on delete cascade
)
engine=InnoDB
;

create index AccountsUsers_Accounts_ID_fk
    on AccountsUsers (AccountsID)
;

Paul Campbell Paul Campbell · Accepted Answer · 2018-04-21T16:39:08

From the documentation

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.

In other words, if you don't already have the required indexes on the columns of your referencing table (AccountsUsers), MySQL will create them for you.

If the columns in the referenced tables (Accounts and Users) are not indexed you will get an error. Your's look like they will be Primary Keys on their respective tables, so you should be fine.

MySQL table foreign key indexes on primary index columns

2 Answers